How to Insert JPEG Images Beside Matching Filenames in Excel Using VBA
Question details
The user wants to use an Excel VBA macro to automatically insert JPEG images into column C, aligned with their corresponding filenames listed in column A, and resize each image to 100x100 pixels.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Automating the insertion and resizing of multiple images into specific cells based on text strings (filenames) within a worksheet.
- Observed behavior
- Images need to be inserted, aligned to the top-left of the target cell, and sized to exact pixel dimensions (100x100) using VBA.
Ensure that your Excel workbook is saved as a Macro-Enabled Workbook (.xlsm) and that you have the complete, accurate folder paths for all the JPEG images you intend to insert.
Use the Shapes.AddPicture Method in VBA
This method allows you to precisely insert, position, and resize images based on file paths corresponding to the filenames in your worksheet.
The Shapes.AddPicture method is the most reliable way to insert images via VBA. It requires the full file path and allows you to set the Left, Top, Width, and Height parameters directly during insertion.
Press ALT + F11 in your spreadsheet to open the Visual Basic for Applications (VBA) editor.
In the top menu, click on Insert and select Module to create a blank script window for your macro.
Create a loop that iterates through the rows containing filenames in column A. Ensure your script builds the full file path by combining your root folder path with the cell value.
Use the syntax: Worksheets("Sheet1").Shapes.AddPicture oFile.Path, False, True, Worksheets("Sheet1").Range("C" & i).Left, Worksheets("Sheet1").Range("C" & i).Top, 100, 100. This places the image in column C aligned with the current row (i) and sizes it to 100x100 pixels.
Press F5 or click the Run button to execute the code. The images will automatically populate next to their corresponding filenames.
Automate Image Insertion Smoothly with WPS Spreadsheet
WPS Office provides a highly compatible VBA environment. You can run standard Excel macros seamlessly in WPS Spreadsheet to automate complex image insertion tasks.
- 1. Install WPS Office: Download and install WPS Office, then open your macro-enabled spreadsheet in WPS Spreadsheet.
- 2. Open the VBA Editor: Navigate to the Developer tab and click on 'VBA Editor' to access the scripting environment.
- 3. Run the Macro: Paste your Shapes.AddPicture macro into a new module and hit Run to instantly insert and resize all matching JPEG images.
Frequently Asked Questions
Why is the Shapes.AddPicture macro returning an error?
This usually happens if the file path is incorrect, the image file does not exist in the specified directory, or the file extension (.jpg) is missing from the string. Verify that your macro concatenates the folder path and the filename properly.
How do I make the inserted image fit the cell exactly instead of fixed 100x100 pixels?
Instead of hardcoding 100 for width and height, you can dynamically set these parameters to match the target cell. Use Worksheets("Sheet1").Range("C" & i).Width and Worksheets("Sheet1").Range("C" & i).Height in the AddPicture method.
Can I use this VBA code for PNG images as well?
Yes, the Shapes.AddPicture method supports various image formats including JPEG, PNG, and BMP. Just ensure your macro references the correct file extension in the file path string.
