Excel VBA: Insert Pictures from File Names in a Column
Question details
The user needs to insert pictures into a worksheet by matching file names listed in one Excel column with image files stored in a folder.
- Product
- Microsoft Excel VBA
- Device & OS
- not provided
- Scenario
- Image file names are listed in column B without extensions, and each matching picture should be placed in the adjacent cell in column C.
- Observed behavior
- The macro must locate each matching image, insert it beside the corresponding file name, and configure the picture to move and resize with its destination cell.
Confirm that the image files are all stored in the same folder and that the folder path ends with a backslash. Check the image extension, such as .png, and verify that file names in column B match the files without spelling or spacing differences.
Use VBA to match file names and insert pictures into adjacent cells
Use this solution when image names are listed in column B and the corresponding files are stored in one folder with the same extension.
The macro reads each nonblank file name in column B, adds the .png extension, checks whether the file exists, and inserts the picture in the cell to the right. Setting Placement to xlMoveAndSize keeps each image aligned with its destination cell when rows or columns are resized.
In Excel, press Alt+F11, select Insert > Module, and create a standard module in the workbook that contains the file-name list.
Paste the following code into the module. Replace the folderPath value with the actual image folder and change fileExtension if the files use another format such as .jpg. Sub InsertPicturesFromFileNames() Const folderPath As String = "C:\Users\javog\Pictures\Diversen\" Const fileExtension As String = ".png" Dim ws As Worksheet Dim sourceRange As Range Dim cell As Range Dim fileName As String Dim fullPath As String Dim picture As Shape Set ws = ActiveSheet Set sourceRange = ws.Range("B2", ws.Cells(ws.Rows.Count, "B").End(xlUp)) Application.ScreenUpdating = False For Each cell In sourceRange If Len(Trim$(CStr(cell.Value))) > 0 Then fileName = CStr(cell.Value) If LCase$(Right$(fileName, Len(fileExtension))) <> LCase$(fileExtension) Then fileName = fileName & fileExtension End If fullPath = folderPath & fileName If Len(Dir(fullPath)) > 0 Then Set picture = ws.Shapes.AddPicture( _ Filename:=fullPath, _ LinkToFile:=False, _ SaveWithDocument:=True, _ Left:=cell.Offset(0, 1).Left, _ Top:=cell.Offset(0, 1).Top, _ Width:=-1, _ Height:=-1) picture.Placement = xlMoveAndSize End If End If Next cell Application.ScreenUpdating = True End Sub
Return to Excel with Alt+F11, click a cell in the worksheet, press Alt+F8, select InsertPicturesFromFileNames, and choose Run. Matching images should appear in column C beside the names in column B.
Insert File-Based Pictures in WPS Spreadsheets with VBA
Use WPS Spreadsheets to open a compatible workbook, run a VBA macro that matches file names with images, and place each picture beside its source name. The macro can also set pictures to move and resize with their cells.
- 1. Open the workbook: Launch WPS Office, open the spreadsheet containing the file names, and confirm that the names are in column B.
- 2. Open the macro editor: Use the Developer tools or VBA editor available in your WPS Office edition, then choose Insert > Module.
- 3. Add the picture macro: Paste the file-matching VBA macro, update the folder path and image extension, and save the workbook in a macro-enabled format when required.
- 4. Run and verify: Run the macro from the macro dialog and confirm that each matching picture appears in the adjacent cell and follows cell movement and resizing.
Frequently Asked Questions
Which column should contain the picture file names?
The sample macro reads file names from column B beginning at B2 and inserts pictures into the adjacent cells in column C. Change the B references and cell offset if your data uses different columns.
Do the file names need extensions in the worksheet?
No. The sample appends .png automatically when the name does not already end with that extension. Change fileExtension to .jpg or another extension, or include the full extension in the cells.
Why does the macro insert no pictures?
Verify that folderPath points to the correct folder, ends with a backslash, and uses the correct spelling and extension. The macro skips files when Dir cannot find the constructed path.
How can I make inserted pictures follow row and column changes?
Set each inserted Shape object's Placement property to xlMoveAndSize. This makes the picture move with its cell and resize when the cell dimensions change.
Can the macro use a different destination column?
Yes. The expression cell.Offset(0, 1) places the picture one column to the right of the file name. Increase the second value to place it farther away, or replace it with a specific destination range.
