Excel VBA: Inserting a Picture from the Folder – 3 Methods

You have pictures in a specific folder and want to insert one of these pictures in your Excel worksheet using a VBA code.

Follow the steps to open the Visual Basic Editor.

• Go to the Developer tab.
• Click Visual Basic.

• In the Visual Basic for Applications window, click Insert and select Module.

Method 1 – Inserting a Picture by Name from a Folder Using a VBA Code in Excel

• Select a cell in the Active Worksheet to insert the picture.

• Open the Visual Basic Editor and enter 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

The Filename property is empty in the code. To get the picture location:

• Go to the folder that contains the picture.
• Hold  Shift key and right–click the picture.
• Choose “Copy as path”.

• Paste the picture location in the Filename property in the code.

Filename:="D:\pictures\coffee1.jpg", _

• Run the code by pressing F5.

• This is the output.

Code Explanation:

The Shape.AddPicture method inserts the picture from an existing file and returns a shape object. The syntax 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 in which it will be 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 in its original size.

Method 2 – Inserting Multiple Pictures into a Worksheet from a Folder Using VBA in Excel

Scenario:
You have a list of Coffee items and want to insert the pictures for each of these items in a Column.

Solution:

• Select a cell (C4, here) to insert the first picture.

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

• Run the code by pressing F5.
• In the Open window, choose the Folder containing the pictures.
• Select the pictures to be inserted. Here, all pictures.
• Click Open.

• This is the output.

Method 3 – Running a VBA Code to Insert a Picture from a Folder Based on a Cell Value

Scenario:
In B4, create a Dropdown list with 4 coffee items- coffee1, coffee2, coffee3, and coffee4.

• Select B4.
• Go to the Data tab and select Data Validation.

• In the Data Validation window, click the Settings option.
• In Allow, choose List.

• In the Source input box, enter the names of the items separating them by commas.
• Click OK to save the Settings.

• A dropdown list with 4 coffee items was created.

Task:
To insert the picture of the selected coffee item in the dropdown list in B5:

Solution:
Enter the following code in 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

coffee3 is selected in the item list, and the code automatically inserted the associated picture from its defined location.

The selected item is changed to coffee2 and the output is in the following:

Code Explanation:

• The cell reference of the dropdown list is set.

If Target.Address = Range("B4").Address Then

• The picture location is set:  the folder address (“D:\pictures\”),  the item name in B4, and the file extension (.jpg).

LocationPic = "D:\pictures\" & Range("B4").Value & ".jpg"

• Sets the cells to insert the picture.

With Range("B5")

Notes

• Set the height and width of the inserted pictures:

 Width: =70, Height:=70

• To view the code, right-click the Sheet Name and select View Code.

Download Practice Workbook

Download the practice workbook to exercise.

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