primaryteach
02-10-2010, 04:15 AM
Hi everyone,
I have some code that works in Excel 2000 to choose a file to embed into a document. This seems to work well by leaving an icon in a specific cell (here it is L26) that you can click on to open up the embedded file.
Sub Embed()
Dim vFile As Variant
ActiveSheet.Unprotect "pass"
Range("L26").Select
vFile = Application.GetOpenFilename("All Files,*.*", Title:=" Find file to insert")
If LCase(vFile) = "false" Then Exit Sub
ActiveSheet.OLEObjects.Add(Filename:=vFile, Link:=False, DisplayAsIcon:=True, IconLabel:=vFile).Select
ActiveSheet.Protect "pass", _
Contents:=True, _
UserInterfaceOnly:=True
ActiveSheet.EnableAutoFilter = True
ActiveSheet.EnableOutlining = True
End Sub
However, when I transfer this code into Excel 2007 it leaves just a large white rectangle with no embedded object.
I've used the macro recorder to see if I could spot and differences between the code but it seems identical, except that my code asks the user to choose a file instead of a hard-coded file path.
I've also removed any protection/enabling options to see if that made a difference - but this did nothing to improve on the situation.
Has anyone got any suggestions as to how to make it work within Excel 2007?
Cheers,
Simon
I have some code that works in Excel 2000 to choose a file to embed into a document. This seems to work well by leaving an icon in a specific cell (here it is L26) that you can click on to open up the embedded file.
Sub Embed()
Dim vFile As Variant
ActiveSheet.Unprotect "pass"
Range("L26").Select
vFile = Application.GetOpenFilename("All Files,*.*", Title:=" Find file to insert")
If LCase(vFile) = "false" Then Exit Sub
ActiveSheet.OLEObjects.Add(Filename:=vFile, Link:=False, DisplayAsIcon:=True, IconLabel:=vFile).Select
ActiveSheet.Protect "pass", _
Contents:=True, _
UserInterfaceOnly:=True
ActiveSheet.EnableAutoFilter = True
ActiveSheet.EnableOutlining = True
End Sub
However, when I transfer this code into Excel 2007 it leaves just a large white rectangle with no embedded object.
I've used the macro recorder to see if I could spot and differences between the code but it seems identical, except that my code asks the user to choose a file instead of a hard-coded file path.
I've also removed any protection/enabling options to see if that made a difference - but this did nothing to improve on the situation.
Has anyone got any suggestions as to how to make it work within Excel 2007?
Cheers,
Simon