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)
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
Outbound References