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