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.
- Finally, run the code by pressing F5.
- We’ve successfully inserted a picture named “coffee1” in an Excel worksheet from the folder.
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)
LinkToFile = msoFalse
made the picture an independent copy of the file.
saved the linked picture with the document where it was inserted.
inserted the picture at the top-left corner of the selected cell.
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.
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.
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.
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.
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.
We want to insert the picture of the selected coffee item in the dropdown list in cell B5.
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.
- 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.
- 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.
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.