Objective
To import multiple jpeg images in excel file using VBA Excel.
Approach
Here first we check if the image source folder exists or not, if not then code will exit. If folder exists, then code will continue to execute.
Then we loop through the source folder which contains the image files. We loop through each file, first we check if the file is a jpeg image or not. If the file type is jpeg, then the image file is inserted in the active workbook, in this case all the images will be inserted in the macro file itself. So, this loop continues for all the images.Once the image is stored, it is formatted.Image’s alignment ,height and width are changed.
In the code below, first we have declared a variable which contains the path where all the jpeg images are stored. Then we have initialized the variable LeftPosition which contains the alignment from left. Then we also initialized the variable TopPosition which contains the alignment from top.
We had also stored the width and height of the image in 2 variables, which are ImageWidth and ImageHeight. These variables will be used to change the formatting of the imported images. These variables help us resize the images which were inserted.
Code
Sub InsertMultipleImages()
Dim objFileSystem As Object
Dim SourceFolder As String
Set objFileSystem = CreateObject("Scripting.FileSystemObject")
Dim LeftPosition As Long, TopPosition As Long, ImageWidth As Double, ImageHeight As Double
Dim MyImageFile As Object
'Path of the folder where images are locted
SourceFolder = "C:\Users\myusername\Desktop\Image_Folder\"
'Initialize the left alignment
LeftPosition = 200
TopPosition = 20
ImageWidth = 150
ImageHeight = 150
'Check if source folder exists
If objFileSystem.FolderExists(SourceFolder) = True Then
'Looping through each file in the source folder
For Each MyImageFile In objFileSystem.GetFolder(SourceFolder).Files
'Check for importing only JPG files
If MyImageFile.Type = "JPG File" Then
'Insert JPG files one by one
ActiveWorkbook.Worksheets("sheet1").Shapes.AddPicture MyImageFile, True, True, LeftPosition, TopPosition, ImageWidth, ImageHeight
TopPosition = TopPosition + 170
End If
Next MyImageFile
Else
MsgBox "Source folder does not exist"
End If
MsgBox " All files are inserted in this workbook"
Set objFileSystem = Nothing
End Sub
In the image below we can see that we have imported 3 images in sheet 1 of the the excel macro file. All of them are at equal distance from each other and all of them have the same size as shown in the image.
Reference
https://docs.microsoft.com/en-us/office/vba/api/excel.shapes.addpicture
https://docs.microsoft.com/en-us/office/vba/language/reference/user-interface-help/filesystemobject-object
