Excel VBA: Insert Picture from Folder (3 Methods)

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.

How to Insert Picture from Folder Using Excel VBA: 3 Methods

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

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

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

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 coffee2 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 sets 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")

Notes

• We can easily set the height and width of the inserted pictures as per our requirements. 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.

Download Practice Workbook

Download this practice workbook to exercise while you are reading this article.

Conclusion

Now, we know how to insert pictures in Excel from a folder using VBA code with different methods. Hopefully, it will help you to use these methods more confidently. Any questions or suggestions don’t forget to put them in the comment box below.

Related Articles

• How to Insert an Image into a Cell with Excel VBA
• How to Insert Picture from URL Using VBA in Excel
• Insert Pictures Automatically Size to Fit Cells with VBA in Excel