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:
- Embedded Object – insert the source file into Excel
- 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:
- Display file with an icon, double click on the icon to open the file
- 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
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.
Click on Browse button to select a file
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.
If you have checked the box “Link to file“, the file label will be displayed as the full file path.
If you don’t like the icon or caption, right click on the file > Document Object > Convert > Change icon
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.
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.
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) 'to arrange the files to display vertically instead of horizontally, update to Rng.Offset(1, 0)
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
Now the files are inserted with cell width and height adjusted
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
Wyman,
How should I proceed if I only want a hyperlink instead of a file icon? (VBA example)
Thanks,
Brice
Hi Brice, I updated the post for your reference
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
Sorry,
How can I save it if each file is from different type?
Thanks.
Juan Carlos
Hi Juan, I have updated my post in answer to your question
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?
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
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!
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
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.
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
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 😀
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 🙂
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
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"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
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
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.
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
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.
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!
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
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!
Hey, fantastic code!
Is there a way to stack the multiple files vertically instead of horizontally?
I have added a comment in the code to guide you how to display vertically
Hi Wyman
Is it possible to insert a folder – that when clicked, would redirect to the actual File Explorer containing the different files – instead of inserting a file?
Thank you
In that case you just insert a hyperlink and link to the folder path