Attach file as icon in excel using macro

Question

I'm trying to create a macro for use within Excel, that allows the user to attach a file as an icon (as they wont be able to use the insert object method).

I've got to the point where the macro successfully attaches the file but the icon ends up being a blank white rectangle larger than what would be produced normally.

Dim File As Variant

   File = Application.GetOpenFilename

   ActiveSheet.OLEObjects.Add(Filename:=File, Link:=False, DisplayAsIcon:=True).Select

The third line is based on the following recorded macro but obviously returns specific values for the icon and file name to be used.

ActiveSheet.OLEObjects.Add(
     Filename:= "filepath" , 
     Link:=False, 
     DisplayAsIcon:=True, 
     IconFileName:= "path for icon", 
     IconIndex:=0, 
     IconLabel:="filename").Select

Is there any way to get the IconFileName and the IconLabel from the File = Application.GetOpenFilename for use within the ActiveSheet code?

Also, is there any way to specify the size of the icon that is then displayed?

Thanks in advance.

Answer 1

You can specify Height and Width and Top and Left in the .Add function.

From this source: https://docs.microsoft.com/en-us/office/vba/api/excel.oleobjects.add

IconFileName

A 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.

To use the default icon, just get rid of IconFileName

Also, you have to unlock the default LockAspectRatio if you want to be able to stretch the icon freely:

With ActiveSheet.OLEObjects.Add( _
     Filename:="C:\Users\Tyrael\Documents\Casa Del Mar.pdf", _
     Link:=False, _
     DisplayAsIcon:=True, _
     Top:=[D1].Top, _
     Left:=[D1].Left)
         .ShapeRange.LockAspectRatio = msoFalse
         .Height = 500
         .Width = 500
End With

Results: