Want to become an expert in VBA? So this is the right place for you. This blog mainly focus on teaching how to apply Visual Basic for Microsoft Excel. So improve the functionality of your excel workbooks with the aid of this blog. Also ask any questions you have regarding MS Excel and applying VBA. We are happy to assist you.

Insert several images to a worksheet using VBA


    In an earlier post I explained how to insert one image to an Excel worksheet. If you want to learn how to insert one image you can go to that post from below link.

Insert an Image to Excel Worksheet using VBA

    Today's post is about how to modify that code to insert several images to a sheet. So I'm going to create very simple template to explain this. We will not have any text description or content in our sheet. This will only have images. But you can modify it to create your own template with lot of images, descriptions etc.

Assume below is the sheet where we need to insert our images.

And I have all images I want to insert in one folder. I have put them all to one folder so I can easily input file paths. But you don't need to put the all images to one folder.

So we have five images to insert. In our earlier post about inserting one image, we put the file path inside the code. Like that we can put paths of All the images in the code. But then it is not user friendly. Because our file may used by a person who doesn't know anything about VBA. So what we going to do is add another sheet call "File Paths" to this work book. And specify a column to put file paths of relevant images.

So now it is not a problem even our user doesn't know anything about VBA. Also we can put a button in this sheet to call our macro. So once the paths are entered, user can click that button. Then images will be inserted automatically to template sheet. And user will be directed to that sheet.

So now we need to develop a macro to do this. Actually this is much similar to code we used for insert one image. What we need to do is add a looping method to get paths of files one by one.
So here is the full code.

Sub InsertSeveralImages()

Dim pic_Path As String 'File path of the picture
Dim cl As Range, Rng As Range
Dim WS_Templte As Worksheet

Set WS_Templte = Worksheets("Template")
Set Rng = Worksheets("File Paths").Range("C3:C7")

pastingRow = 2

For Each cl In Rng

    pic_Path = cl.Value
    Set InsertingPicture = WS_Templte.Pictures.Insert(pic_Path)
   
    'Setting of the picture
    With InsertingPicture
        .ShapeRange.LockAspectRatio = msoTrue
        .Height = 100
        .Top = WS_Templte.Rows(pastingRow).Top
        .Left = WS_Templte.Columns(3).Left
    End With
   
    pastingRow = pastingRow + 8
         
Next cl
       
Set myPicture = Nothing

WS_Templte.Activate

End Sub

Below is a brief explanation of the code.

First we need to define the variables

Dim pic_Path As String 'File path of the picture
Dim cl As Range, Rng As Range
Dim WS_Templte As Worksheet

We use "Template" sheet to insert the images.

Set WS_Templte = Worksheets("Template")

And Range("C3:C7") of "File Paths" sheet is the range where user put the file paths of the images.

Set Rng = Worksheets("File Paths").Range("C3:C7")

We use below line to define the row we going to insert our first image.

pastingRow = 2

pastingRow  value should be incremented by suitable amount before go to next loop to give space to insert  images. So we have put space of 8 rows in this example to insert one image. And you can notice that I have used below line before go to next loop.

pastingRow = pastingRow + 8

And we have used for each loop in this example to insert each and every image.



For Each cl In Rng
       
Next cl

And there is a coding part inside that For each loop. I have explained that part form my earlier post.

WS_Templte.Activate

And above line will direct the user to sheet where the images are inserted.

Contact Form

Name

Email *

Message *

Popular Posts