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 business analysis, project management, and also 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 in Access / Excel
- Microsoft Specialist in MS Project
- Microsoft Technical Associate
- Microsoft Certified Professional
- IBM SPSS Specialist

23 thoughts on “Excel insert file with and without VBA

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

  2. 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. 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!

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

      1. hmmm i too has this same problem, but its weird that the imported file is still protected(locked), all i did is sort of like what you suggest :

        Public Sub insertFile()
        Sheet1.Unprotect “1234”
        ‘your import file code
        Sheet1.Protect “1234”
        End sub

        im still new to vba excel, can you give me(us) some light? anyway thanks for the code 😀

        1. edit: i found out that using this code solve the problem : ActiveSheet.OLEObjects.Locked = False
          just insert it after the import file code (in my case i insert it after the “IconLabel:=extractFileName(fpath(i))” code).

          just make sure you that you still have the “protect – unprotect sheet” and it should be working. cheers 🙂

  4. 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. 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"

  5. I am running the code to bring in .csc or .txt file. When I click save, the file name is renaming the file to some generic name. e.g. FileName.csc gets renamed to random generated 6 digit alpha-numeric.csc What code is required to maintain the original file name. Note this does not occur with .docs or .xlsx

    1. Hi Chris, I tried importing txt and csc in Excel 2016 but it works fine for me even if I use Chinese as file name. I suspect if your file names are non-English

      1. Sorry, I should have mentioned I am using Office 2010. The file names are English. If I import abc.txt, when I click save it renames to a random generated file name. Oddly it does not do this for .xlsx. docx etc thanks again.

      2. Link:=True, _
        IconFileName:=”notepad.exe”, _

        I made a couple of changes Excel 2010 VB, setting the link to true and from excel.exe to notepad.exe. It doesn’t change the icon for different apps with this change but at least it is keeping the original .csc , .txt, etc file names

        1. Hi Chris, I guess the behavior of older version Excel is different from the newer version. I haven’t got the 2010 version in my computer so I cant reproduce the error to find out what’s wrong.

  6. Wyman Yay! this is the closest I have come to finding a solution for my problem. Many many thanks!
    I still have an issue: when I select *.jpgs along with other files types (*.pdf, *.docx, *.xls?) the icon size is all over the place, from wide, to narrow, to no icon, to no filename (can send screenshot if you like).
    Is there some way to allow my users to select “Any” file type and constrain the display icon to the orderly display you have in your screenshot?
    I am using Excel 2010, so I know you cannot test specifically for this platform, but any suggestions are welcomed.
    Again, many thanks for pushing this project forward for me!

    1. Hi Jewel, I cannot tell whether it is your coding problem or it is the 2010 problem from your description, can you send me a sample of the VBA workbook and the files you want to select, I will try to reproduce the problem. My email is scammera1@yahoo.com.hk

  7. Wynab (scammera1),
    I sent files and code via email.
    I really love the way you break out your code – makes it so easy to interpret. And again, thank you for pointing me in the right directions!

Leave a Reply

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