Excel insert file with and without VBA

This Excel tutorial explains how to insert file (insert object) in Excel using VBA and without using VBA.

Excel insert file object (non-VBA)

In Excel, insert file is known as insert object, there are two kinds of insert:

  1. Embedded Object – insert the source file into Excel
  2. Linked Object – insert the link of file into Excel, not the actual source file

In terms of display of the inserted file, there are also two kinds:

  1. Display file with an icon, double click on the icon to open the file
  2. Display the file contents directly in spreadsheet

The below screenshots were captured in Excel 2013, but the navigation is more or less the same for other versions.

Click on INSERT tab > Object

Excel insert file 01

 

There are two tabs under Object box: Create New and Create from File

Since we insert file but not create new file,  select Create from File.

Excel insert file 02

 

Click on Browse button to select a file

Excel insert file 03

There are two check boxes on the right hand side:

Link to file If unchecked, a copy of the file is inserted into the Excel (file size will also increase), changes are made in the copy but not to the original file.

If checked, link to the path of the original file. Changes are made in the original file.

Display as icon If unchecked, some file types are opened in the spreadsheet. If checked, the file is displayed as icon and you need to double click to view.

 

Now the file is inserted. Double click on the object to open the file.

Excel insert file 04

 

If you have checked the box “Link to file“, the file label will be displayed as the full file path.

Excel insert file 05

 

If you don’t like the icon or caption, right click on the file > Document Object > Convert > Change icon

Excel insert file 08

Excel insert file object (VBA)

Suppose you have created a template as below, you want users to click on the Browse button and then the attachment shows up in Cell D3.

Excel insert file 06

Insert the below Procedure in a Module and link the button to the Procedure.

Public Sub insertFile()
    
    'Select the cell in which you want to place the attachment
    Range("D3").Select
       
    'Get file path
    fpath = Application.GetOpenFilename("All Files,*.*", Title:="Select file")
    If LCase(fpath) = "false" Then Exit Sub
        
    'Insert file
    ActiveSheet.OLEObjects.Add _
    Filename:=fpath, _
    Link:=False, _
    DisplayAsIcon:=True, _
    IconFileName:="excel.exe", _
    IconIndex:=0, _
    IconLabel:=extractFileName(fpath)
    
End Sub


Public Function extractFileName(filePath)

    For i = Len(filePath) To 1 Step -1
        If Mid(filePath, i, 1) = "\" Then
        extractFileName = Mid(filePath, i + 1, Len(filePath) - i + 1)
        Exit Function
        End If
    Next

End Function

As you browse the target file, the file is inserted into Cell D3.

Excel insert file 07

 

Note that in the Macro, I created a custom Function to extract the file name from the file path, because I found that sometimes the file label is displayed as full path and sometimes just file name although I set the Link property to False.

On the other hand, I select Excel icon as the default icon for all files, but when you save the workbook, the icons will automatically change according to the file type.

Excel insert multiple file objects (VBA)

Application.GetFileName Method has an argument called MultiSelect, which allows users to select multiple files and return an array of file names. I modify the above procedure a little bit, if you have selected multiple files, each object is inserted on the right hand side of the starting cell.

Public Sub insertFile()
    Set Rng = Range("D3")  'Name the cell in which you want to place the attachment
    Rng.RowHeight = 56
    On Error Resume Next
    fpath = Application.GetOpenFilename("All Files,*.*", Title:="Select file", MultiSelect:=True)
    For i = 1 To UBound(fpath)
        Rng.Select
        Rng.ColumnWidth = 12
        ActiveSheet.OLEObjects.Add _
        Filename:=fpath(i), _
        Link:=False, _
        DisplayAsIcon:=True, _
        IconFileName:="excel.exe", _
        IconIndex:=0, _
        IconLabel:=extractFileName(fpath(i))
        Set Rng = Rng.Offset(0, 1)
    Next i
End Sub

Public Function extractFileName(filePath)
    For i = Len(filePath) To 1 Step -1
        If Mid(filePath, i, 1) = "\" Then
        extractFileName = Mid(filePath, i + 1, Len(filePath) - i + 1)
        Exit Function
        End If
    Next
End Function

Let’s say we want to import the below files of different file types, click on Open button

excel-vba-insert-object-001

 

Now the files are inserted with cell width and height adjusted

excel-vba-insert-object-002

Again, if you save the workbook, the icons will automatically change according to the file type.

Excel insert file with hyperlink (VBA)

Instead of  inserting an object, you can also add a hyperlink that links to the file path using VBA hyperlink function.

In the below example, I insert a hyperlink that links to c:\example.docx

Public Sub add_hyperlink()
    ActiveSheet.Hyperlinks.Add anchor:=Range("A1"), Address:="C:\example.docx", ScreenTip:="Open file", TextToDisplay:="example.docx"
End Sub

Excel VBA Hyperlinks Function 001

Outbound References

https://support.office.com/en-us/article/Link-or-embed-an-Excel-worksheet-41bf021e-ba7c-44ef-9914-0d7e88062257

 

 

Wyman W
Wyman is a Business Analyst based in Hong Kong, specialized in creating custom Function and Sub solutions, and is proficient in report automation with Access.

He is also a:
- Microsoft Most Valuable Professional (Excel)
- Microsoft Community Contributor
- Microsoft Office Specialist
- Microsoft Specialist
- Microsoft Technical Associate
- Microsoft Certified Professional
- IBM SPSS Specialist

13 thoughts on “Excel insert file with and without VBA

  1. Brice says:

    Wyman,
    How should I proceed if I only want a hyperlink instead of a file icon? (VBA example)
    Thanks,
    Brice

  2. Wyman W Wyman W says:

    Hi Brice, I updated the post for your reference

  3. Juan Carlos Medina Ruiz says:

    Hi Wyman,

    Your code runs quite good!

    I was wondering about to add multiples files from different types of files. For example: word, pdf and excel.

    How can I modify your code to do that?.

    Thanks.
    Juan Carlos

  4. Juan Carlos Medina Ruiz says:

    Sorry,

    How can I save it if each file is from different type?

    Thanks.
    Juan Carlos

    1. Wyman W Wyman W says:

      Hi Juan, I have updated my post in answer to your question

  5. Christa says:

    When inserting various emails into Excel using you code above, the correct subject lines show up for the icon label, but, when I save the Excel file, the subject lines are replaced with a computer generated alphanumeric. Is there any way to amend the code above to make the original subjects of the emails stay after they are saved?

    1. Wyman W Wyman W says:

      Hi Christa, I google your question and found someone has the same problem a few years ago but couldn’t find a solution. I tried it myself in Excel 2016 but it works fine, my guess is it happens for the older versions of Excel. I also can’t find a property of the OLEobject that can change the label.
      https://msdn.microsoft.com/en-us/library/office/ff840244.aspx

      1. Christa says:

        Hi Wyman. Thank you! I found a solution for the problem. I had to set the Link: property in the ActiveSheet.OLEObjects.Add to true and it kept the email subject line after I saved the Excel spreadsheet. Hopefully, this will help anyone else with an older version of Excel!

  6. Wyman W Wyman W says:

    Hi Christa, note that setting link to true will link to object to the actual file, if thats ok for you
    https://msdn.microsoft.com/en-us/library/office/ff195728.aspx

  7. Sean says:

    Hi – how can I import the objects so that they are unlocked when I protect the worksheet? I don’t want all objects on the worksheet to be unlocked when I protect the worksheet, just the ones imported using this code.

    1. Wyman W Wyman W says:

      Hi Sean, I suggest you to write a Macro to
      1) unlock worksheet
      2) run the import Macro
      3) protect worksheet

      Sub proct()
      pwd = "abc123"
      Sheets("Sheet1").Unprotect (Password = pwd)
      'Do something
      Sheets("Sheet1").Protect (Password = pwd)
      End Sub

  8. Ram says:

    Hi,

    I tried using your code. But when i attach PDF’s the icon doesn’t change upon saving. I can only see the excel icon. Any help on this

    1. Wyman W Wyman W says:

      Hi Ram, you can consider changing the IconFileName argument to force it to use PDF icon. First of all, locate the PDF icon under C:\Windows\Installer\
      For me, the icon is under
      C:\Windows\Installer\{AC76BA86-7AD7-1028-7B44-AC0F074E4100}\PDFFile_8.ico

      Then my VBA code will be
      IconFileName:="C:\Windows\Installer\{AC76BA86-7AD7-1028-7B44-AC0F074E4100}\PDFFile_8.ico"

Leave a Reply

Your email address will not be published. Required fields are marked *