Images are widely used in excel reports to make it more interactive and presentable. Thus, images are mostly used in various excel reports. Sometimes, we use name of images in excel macro to automate report generation process. Therefore, it is very important to use required naming convention to each image used in the workbook. Preferably, some sort of images validation would be the best solution to avoid runtime errors which can occured by incorrect image name. Using excel macro code we can list all images from the workbook which is further used in image name validation.
Here is the excel macro which can be use to list all images from the workbook:
Sub List_Images_In_Workbook() ' Author: Dreams24 ' Written for VBA Tricks and tips blog ' https://www.vbatricksntips.com 'Declare Variable Dim Pic As Object Dim sht As Worksheet Dim i As Integer On Error GoTo Err: Application.ScreenUpdating = False i = 2 ThisWorkbook.Sheets("Sheet1").Cells(1, 1).Value = "Sr. No" ThisWorkbook.Sheets("Sheet1").Cells(1, 2).Value = "Sheet Name" ThisWorkbook.Sheets("Sheet1").Cells(1, 3).Value = "Picture Name" 'Loop through all sheets in a workbook For Each sht In ActiveWorkbook.Worksheets sht.Activate 'Loop through all picture in a worksheet For Each Pic In ActiveSheet.Pictures ThisWorkbook.Sheets("Sheet1").Cells(i, 1) = i - 1 ThisWorkbook.Sheets("Sheet1").Cells(i, 2) = sht.Name ThisWorkbook.Sheets("Sheet1").Cells(i, 3) = Pic.Name i = i + 1 Next Pic Next sht ThisWorkbook.Sheets("Sheet1").Activate Application.ScreenUpdating = True Err: If Err.Number <> 0 Then MsgBox Err.Description End If MsgBox "All images are listed in Sheet1", vbInformation End Sub