This article illustrates how to insert a picture in Excel from the storage folder using VBA code with 2 different methods. We’ll be able to insert single or multiple pictures with these methods.
Download Practice Workbook
Download this practice workbook to exercise while you are reading this article.
3 Methods to Insert Picture from Folder Using Excel VBA
Let’s say, there are a bunch of pictures in a specific folder on the computer storage. We want to insert one of these pictures in our excel worksheet using VBA code.
The following section describes how to open and write code in the visual basic editor.
Write Code in Visual Basic Editor
Follow the steps to open the visual basic editor and write some code there.
- Go to the Developer tab from the Excel Ribbon.
- Click the Visual Basic option.
- In the Visual Basic for Applications window, click the Insert dropdown to select the New Module option.
1. Insert Picture by Name from Folder Using VBA Code in Excel
To insert a picture by its name, we need to follow the simple steps below.
- At first, select a cell in the active worksheet where we’re going to insert the picture.
- Open the visual basic editor and then put the following code.
Sub InsertPicture()
ActiveSheet.Shapes.AddPicture _
Filename:= , _
LinkToFile:=msoFalse, _
SaveWithDocument:=msoTrue, _
Left:=ActiveCell.Left, _
Top:=ActiveCell.Top, _
Width:=-1, Height:=-1
End Sub
As we see the Filename property is empty in the code. To get the picture location–
- Navigate to the folder that holds the picture, we need to insert.
- After that, hold down the Shift key and then right–click on the picture.
- Choose the “Copy as path” option.
- Now paste the picture location in the filename property in the code.
Filename:="D:\pictures\coffee1.jpg", _
- Finally, run the code by pressing F5.
- We’ve successfully inserted a picture named “coffee1” in an Excel worksheet from the folder.
Code Explanation:
In our code, we used the Shape.AddPicture method to insert the picture from an existing file. This returns a shape object to return a new picture. The syntax of this method is-
expression.AddPicture(FileName, LinkToFile, SaveWithDocument, Left, Top, Width, Height)
Here,
LinkToFile = msoFalse
made the picture an independent copy of the file.
SaveWithDocument:=msoTrue
saved the linked picture with the document where it was inserted.
Left:=ActiveCell.Left, Top:=ActiveCell.Top
inserted the picture at the top-left corner of the selected cell.
Width:=-1, Height:=-1
Inserted the picture at its original size.
Read More: Insert Pictures in Excel Automatically Size to Fit Cells
2. Insert Multiple Pictures into Worksheet from Folder Using VBA in Excel
In this example, we’ll show how to insert multiple pictures into a worksheet. This time we’ll be able to select one or multiple pictures from the folder itself.
Scenario:
Let’s say we have a list of coffee items on our worksheet. We want to insert the related pictures of these items one after another in a column following a sequence.
Solution:
To do that-
- Select the cell (C4 in this example) to insert the first picture.
- Now copy and paste the following code.
Sub InsertMultiplePictures()
Dim Pictures() As Variant
Dim PictureFormat As String
Dim PicRng As Range
Dim PicShape As Shape
On Error Resume Next
Pictures = Application.GetOpenFilename(PictureFormat, MultiSelect:=True)
PicColIndex = Application.ActiveCell.Column
If IsArray(Pictures) Then
PicRowIndex = Application.ActiveCell.Row
For lLoop = LBound(Pictures) To UBound(Pictures)
Set PicRng = Cells(PicRowIndex, PicColIndex)
Set PicShape = ActiveSheet.Shapes.AddPicture(Pictures(lLoop), msoFalse, msoCTrue, PicRng.Left, PicRng.Top, PicRng.Width, PicRng.Height)
PicRowIndex = PicRowIndex + 1
Next
End If
End Sub
- Running the code by pressing F5 would open up the Open window.
- Navigate to the exact folder location of the picture.
- Select the pictures to be inserted. We’ve selected all the pictures in this example.
- And finally hit the Open button.
- As the output, we’ve successfully selected and inserted the pictures for the coffee items on the worksheet.
Similarly, we could also select and insert only a single picture following the above method.
Read more: How to Insert Picture into Excel Cell (3 Methods)
3. Run a VBA Code to Insert Picture from Folder Based on a Cell Value
In this example, we want to insert pictures dynamically in our worksheet based on a cell value. With the change of the cell value, the inserted picture will change accordingly.
Scenario:
In cell B4, we’ll create a dropdown list with 4 coffee items– coffee1, coffee2, coffee3, and coffee4. Follow the simple steps to create the list.
- Select cell B4.
- Go to the Data tab and select the Data Validation option.
- In the Data Validation window, click the Settings option.
- Under the Allow dropdown, choose the List option.
- Now in the source input box, write down the names of the items separating them by commas, and finally hit OK to save the settings.
- The above steps have created a dropdown list with 4 coffee item names.
Task:
We want to insert the picture of the selected coffee item in the dropdown list in cell B5.
Solution:
Copy and paste the following code into the visual basic editor.
Option Explicit
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim InsPic As Picture
Dim LocationPic As String
If Target.Address = Range("B4").Address Then
ActiveSheet.Pictures.Delete
LocationPic = "D:\pictures\" & Range("B4").Value & ".jpg"
With Range("B5")
Set InsPic = ActiveSheet.Pictures.Insert(LocationPic)
.RowHeight = InsPic.Height
InsPic.Top = .Top
InsPic.Left = .Left
InsPic.Placement = xlMoveAndSize
End With
End If
End Sub
Here we selected coffee3 in the item list and the code automatically inserted the associated picture from its defined location.
We changed the selected item to coffee 2 and the output is in the following screenshot.
Code Explanation:
- In the following line, we set the cell reference of the dropdown list.
If Target.Address = Range("B4").Address Then
- The following line set the picture location. 1st part of the location is the folder address (“D:\pictures\”), then the next part defines the item name coming from cell B4, and the final part is the file extension (.jpg) of the pictures.
LocationPic = "D:\pictures\" & Range("B4").Value & ".jpg"
- With the following line, we set where to insert the picture.
With Range("B5")
Read More: How to Insert Picture in Excel Cell Automatically
Notes
- We can easily set the height and width of the inserted pictures as per our requirement. For example, to set the picture width and height to 70 px, we need to set the value in our code like this.
Width: =70, Height:=70
- To view the code associated with each method, click the right button on the sheet name and select the View Code.
Conclusion
Now, we know how to insert pictures in Excel from a folder using VBA code with different methods. Hopefully, it would help you to use these methods more confidently. Any questions or suggestions don’t forget to put them in the comment box below.
am not able to fetch the image using method 3
Dear NIRMAL
Thank you for taking the time to read this article. I can see that you are facing a problem while executing the code in method 3. I guess It is Run-time error ‘1004’.
Kindly note that you need to modify the file path according to the folder where you are storing your pictures. You also have to specify the type of the image (jpg, jpeg, png, etc.) correctly. So, you should change the LocationPic variable in the code accordingly.
For your convenience, I am attaching a photo indicating the exact portions of the code that you need to re-write.
If you have any more questions, please let us know in the comments.
Regards
Md. Abu Sina Ibne Albaruni
Team ExcelDemy