Macro to insert a object in excel

[Lester from AUS]

Hello, I wonder if anyone can help. I am trying to create a Macro to insert
an object (any type of file .xls.jog.pdf.doc etc etc) in an excel workbook to
a specific location. I want the macro to perform the following excel command:
Select the location of the file to be inserted> select “Insert†from the menu
bar and then > Object > move to tab “Create from List†> tick the box
“display as icon†> and then allow the user to browse and select the file
they wish to insert. I also want to inserted file to be in a specific
location in the worksheet.

Thanks a million excellers!!!

 

[Joel]

The code below works for pdf files. A got the basic code from a learn macro.
the location of the ICON may be different on your PC. don't know enough
about the subject. for a Text file it will display the pdf icon. It looks
like you may havve tto modify the program for each type file you want to add.
check the extension on the file and the use a diffferent ICON for each file
type.


Sub InsertObject()

MyFileName = Application.GetOpenFilename("")

CellLocation = InputBox("Enter Cell Location")

CellLocation = "" + CellLocation + ""

ActiveSheet.Range(CellLocation).Activate

ActiveSheet.OLEObjects.Add(Filename:=MyFileName, Link:=False, _
DisplayAsIcon:=True, IconFileName:= _

"C:\WINNT\Installer\{AC76BA86-7AD7-1033-7B44-A70000000000}\PDFFile.ico", _
IconIndex:=0, IconLabel:=MyFileName).Select
End Sub

 

[Lester from AUS]

Hi Joel,

Sub InsertObject2()

MyFileName = Application.GetOpenFilename("")

ActiveSheet.Range("h15").Activate

ActiveSheet.OLEObjects.Add(Filename:=MyFileName, Link:=True, _
DisplayAsIcon:=True,
IconFileName:="C:\WINNT\Installer\{AC76BA86-7AD7-1033-7B44-A70000000000}\PDFFile.ico", _
IconIndex:=0, IconLabel:=MyFileName).Select

End Sub

 

[Joel]

Lester: Do you need additional help. All you sent was updted code with no
comments.

 

[Lester from AUS]

Joel,

Sorry mate must have deleted the comments by error. The sub worked
perfectly, thanks a lot for your help. I just wanted to let you know that I
needed to make some slight changes as noted in the sub I sent. Basically, I
changed the Link to True and defined a location to attach the file. This now
works for All Files.

Thanks again Joel, you are a champ!!!

 

[Lester from AUS]

Joel,

Sorry mate I still need your help. When I use link=True, the file can not be
opened once emailed. You mentioned that your sub works for PDF files, but it
actually does not. It works for all other files (Excel, word, outlook) but
not PDF. Is this because the setting in my computer? I have tried changing
the IconFileName:=, but still can not get it working for PDF.

Any help would be much appreciated…

Many thanks