Embedding objects Excel 2007

[primaryteach]

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

[markmrw]

i have tested your code with 2007 seems to work for me but only if the sheet is unprotected

[primaryteach]

Thanks Mark for taking a look at my code.

Having rechecked myself, I agree that if I have an unprotected workbook, then the code embeds the file. What isn't working in 2007, but does for earlier versions is giving the object a title and icon - I still just get a large empty rectangle.

Any suggestions on who to define the size, name and icon used for this embedded object? (These might be PDFs, docs, xls, etc.)

[Paul_Hossler]

on my Macro recorder there are 2 additional paramters recorded that seem to allow you to control the icon displayed. When I use them, the xlsm file I embedded shows the familar Excel icon



Sub Embed()
Dim vFile As Variant

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
' IconFileName:="C:\Windows\Installer\{91120000-0030-0000-0000-0000000FF1CE}\xlicons.exe", _
' IconIndex:=0, _

End Sub





IconFileNameOptionalVariantA string that specifies the file that contains the icon to be displayed. This argument is used only if DisplayAsIcon is True. If this argument isn't specified or the file contains no icons, the default icon for the OLE class is used.IconIndexOptionalVariantThe number of the icon in the icon file. This is used only if DisplayAsIcon is True and IconFileName refers to a valid file that contains icons. If an icon with the given index number doesn't exist in the file specified by IconFileName, the first icon in the file is used.

Paul

[primaryteach]

Hi Paul,

Thanks for the post.
I can put a direct link in the vba to specific icon on my hard drive (as your code reveals) but when I use my original code in Excel 2000, the icon changes automatically dependent on the type of file you embed, e.g. photo or PDF (it uses the icon associated with that file type on your PC).

I was hoping for the same effect using Excel 2007 (!), instead it doesn't show an icon at all (unless I hard code a specific icon).

To add more difficulty, I was hoping to pass the workbook to other colleagues, in which case I guess hard-coding a specific location on the hard drive will not work, as it might differ from XP to Vista, Office 2007 to Office 2000, etc.

If this is not possible, is there a way to use a picture already in the worksheet as the icon?

Cheers,

Simon