none
What is the VBA code to insert an Object into an Excel File

    Question

  • Hi,

    I have recently been requested to create a button on an Excel spreadsheet that will insert an Object (usually a MS Word Document but not always) into the Spreadsheet as as Object. The user would need to browse for the document  and select the file, then the file would attach and be displayed as an icon.

     

    In short, the smae as using the Insert > Object >Create From File (Display as Icon) functionalily that is available from the Standard Toolbar.

     

    My VBA skills are extremely limited and I would appreciate any assistance that I can get on this one.

     

    Thanx in advance,

    Amanda

     

    Wednesday, July 18, 2007 5:15 AM

Answers

  • You can use the Add method of the OLEObjects collection for that

     

    Code Snippet

    Sub Insert_OLE_Object()

    Worksheets("Sheet1").OLEObjects.Add Filename:="c:\temp\sample.pdf", Link:=False, DisplayAsIcon:=False, Left:=40, Top:=40, Width:=150, Height:=10
    End Sub

     


     

    Cheers

    Shasur

    Wednesday, July 18, 2007 8:59 AM

All replies

  • You can use the Add method of the OLEObjects collection for that

     

    Code Snippet

    Sub Insert_OLE_Object()

    Worksheets("Sheet1").OLEObjects.Add Filename:="c:\temp\sample.pdf", Link:=False, DisplayAsIcon:=False, Left:=40, Top:=40, Width:=150, Height:=10
    End Sub

     


     

    Cheers

    Shasur

    Wednesday, July 18, 2007 8:59 AM
  • Hi Shasur, thanx fo rthe code but unfortunately I was able to work that one out previously. the issue that I have is that the document that is attached will need to be selected so I cannot have a hard coded Filename in place. I was wanting code that would open the Browse window and the user could select the file.
    Thursday, July 19, 2007 12:50 AM
  • Hi,

    use the common dialog version 6.0 control.

    then write the following code either in a Macro or on a button click.

    dim FPath as string

     

     

    CD1.ShowOpen
      FPath = CD1.Filename

    If FPath <> "" Then
        ActiveSheet.OLEObjects.Add(Filename:=FPath, Link:= _
            False, DisplayAsIcon:=True, IconIndex:=0, IconLabel:=FPath).Select
        Range("G23").Select
    End If

     

    Thursday, July 19, 2007 1:48 PM
  • This code worked well for me in one of my VBA programs on Excel.

     

    Private Sub Browsebttn_Click()

       dim UF as variant

       UF = Application.GetOpenFilename(FileFilter:="Microsoft Excel files(*.xls),*.xls", Title:="DDTS Excel Files")
         txtfile.Text = UF

    End Sub

     

    it opens a browse window. Then you'll need another button to load the file they selected.

     

    Private Sub loadbttn_Click()

        Workbooks.Open Filename:=UF

      End Sub

     

    Or you could always have it loaded by the first button.

     

    Good luck!

    Thursday, July 19, 2007 1:50 PM
  • When utilising this code I received the Expected bject or Variable error against the CD1.ShowOpen code

     

    I have inserted the Microsoft Windows Common Dialog 6.0

     

    Any ideas?

    Monday, July 30, 2007 5:16 AM
  • Are you sure Amanda that you are using CD1 within the scope of the object. At times you would have declared CD1 in a form and would be calling the object in some Bas Module.

     

    This type of error occurs, when the object could not be found.

     

    Regards

    Shasur

    Monday, July 30, 2007 11:17 AM
  • Hey,

     

    Create the CommandButton on your Excel spreadsheet and assign this code to it:

     

    Private Sub CommandButton1_Click()

    Application.Dialogs(xlDialogInsertObject).Show

    End Sub

     

    Best Regards

     

    Cathrine

     

    Monday, July 30, 2007 11:21 AM
  • Hi Catherine,

    Thank you,

    Thank you,

    Thank you,

     

    It worked a treat - and so easy!!

     

    Cheers,

    Mandi

    Tuesday, July 31, 2007 1:12 AM
  • Can u guide if i want to opt two extensions like ppt and pptx or ppt adn jpeg. what should be code instead of (FileFilter:="Microsoft Excel files(*.xls),*.xls", Title:="DDTS Excel Files")
    Friday, August 02, 2013 3:32 AM
  • Here is something that might work. You can still tweak it to filter out by file type as well as change if you want to link or embed. 

    Sub addFile()

    Dim ftopen As Variant

    'get file name from open file dialog box. 
    ftopen = Application.GetOpenFilenameFileFilter:="Adobe PDF Files (*.pdf),*.pdf", Title:="Choose file to attach")

    'if no file chosen then close sub
    If ftopen = False Then
        MsgBox ("No file chosen")
        Exit Sub
    End If

    'insert file into sheet. 
    ActiveSheet.OLEObjects.Add(Filename:=ftopen, Link:=False, _
            DisplayAsIcon:=True, IconFileName:= _
            "C:\WINDOWS\Installer\{AC76BA86-7AD7-1033-7B44-AA1000000001}\PDFFile_8.ico", _
            IconIndex:=0, IconLabel:=str).Activate

    End Sub


    • Edited by Mr. P-Mosh Friday, November 15, 2013 2:13 PM Update
    Friday, November 15, 2013 2:12 PM