VBA & Macro Problems

How to Import and Resize Multiple Pictures in Excel using VBA

Adam DavisAdam Davis Oct 29, 2026 872 views

Question details

The user needs to batch import approximately 100 pictures into a spreadsheet, apply consistent dimensions to all of them, and display their original file names using a VBA macro triggered by a button.

How to Import and Resize Multiple Pictures in Excel using VBA
Product
Excel
Device & OS
not provided
Scenario
Automating the insertion and formatting of a large number of images into a spreadsheet for documentation or reporting purposes without manually resizing each one.
Observed behavior
The goal is to successfully run a macro that fetches images from a designated directory, places them into specific cells, resizes them uniformly, and extracts their file names for reference.
Before you start

Before creating the macro, confirm your source image formats (e.g., JPG, PNG) and verify if you need to extract additional metadata like captions. Ensure all pictures are stored in a single folder on your computer and enable the Developer tab in your spreadsheet settings.

Solution 1Recommended

Create a VBA Macro to Batch Import and Resize Pictures

Write a custom VBA script that loops through an image folder, inserts each picture, applies specific height and width dimensions, and writes the file name in the adjacent cell.

This approach uses the FileDialog object to let you select a folder, then uses a loop to process every image file inside it. You can customize the insertion row, column, and image sizes within the code.

1
Open the VBA Editor

Press 'Alt + F11' on your keyboard to open the Microsoft Visual Basic for Applications window, then click 'Insert' > 'Module' to create a new blank module.

2
Write the folder selection and loop code

Use 'Application.FileDialog(msoFileDialogFolderPicker)' to prompt the user to select the image directory. Then, use the 'Dir' function to loop through all picture files (like .jpg or .png) in that selected path.

3
Insert and resize the pictures

Inside the loop, use 'ActiveSheet.Pictures.Insert' to place the image into the worksheet. Access the picture's ShapeRange properties to set '.LockAspectRatio = msoFalse', and then define your desired '.Height' and '.Width'.

4
Extract the file names

Assign the file name generated by the 'Dir' function to a cell using 'Cells(row_number, column_number).Value = filename', then increment your row counter to move to the next row for the subsequent image.

Create a VBA Macro to Batch Import and Resize Pictures
Adjusting Cell Sizes: To make the presentation cleaner, you may also want to add code that adjusts the row height and column width to match the dimensions of your imported pictures.

Batch Import Pictures Easily with WPS Spreadsheets

WPS Office provides robust support for VBA macros in its Spreadsheets application. You can seamlessly run scripts to batch import, resize, and label hundreds of pictures, making documentation tasks much faster.

  1. 1. Enable the Developer Tab: Open WPS Spreadsheets, go to the settings to enable Developer tools, and click on the Developer tab on the ribbon.
  2. 2. Access the VBA Editor: Click 'VBA Editor' to open the coding environment and paste your batch import macro.
  3. 3. Save and Run: Save your document as a Macro-Enabled Workbook (.xlsm) and insert a button to run your code with a single click.
Fully compatible with Microsoft Excel macro formats (.xlsm)Seamless VBA execution for automating picture insertion and resizingLightweight, fast software with a familiar user interfaceFree to download with comprehensive spreadsheet management tools
microsoft office alternative - wps office

Frequently Asked Questions

Why are my imported pictures distorted after resizing with VBA?

This happens if the aspect ratio is not locked. To keep the original proportions while resizing, ensure your code sets '.ShapeRange.LockAspectRatio = msoTrue' before you apply the new height or width values.

Can I specify which cell the pictures are inserted into?

Yes. You can align the images to specific cells by setting the picture's 'Top' and 'Left' properties to match a cell. For example: 'With pic .Top = Range("B2").Top .Left = Range("B2").Left End With'.

Does this VBA code work on both Windows and Mac?

The 'Application.FileDialog' method used to pick a folder is specific to Windows. If you are using Excel on a Mac, you will need to modify the code to use MacScript or AppleScript for the folder selection prompt.

How do I remove the file extension from the imported file name?

You can use the VBA 'Replace' function or 'Left' function in conjunction with 'InStrRev' to strip out extensions like '.jpg' before writing the file name into the cell.