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