VBA & Macro Problems

How to Arrange Photos from Multiple Folders in Excel using VBA

Maira Mehtab Sep 21, 2026 868 views

Question details

The user needs to construct a VBA logic structure to automate fetching and arranging identically named image files stored in various nested folders into an Excel spreadsheet.

Product: Microsoft Excel
Device & OS: not provided
Scenario: Automating bulk image insertion from multiple nested directories into an Excel workbook layout.
Observed behavior: The specific layout and ordering rules have not been defined yet, requiring a structured approach to determine macro parameters before writing the VBA script.

Before you start

Before writing your VBA script, carefully map out your folder structure and clearly define the exact layout rules for how the images should be displayed in the workbook.

Solution 1Recommended

Define Your VBA Image Import Workflow and Parameters

Since the desired layout and ordering are unspecified, you must clarify four critical parameters to ensure your macro accurately imports and arranges the pictures.

Writing a VBA macro to pull data from multiple directories requires explicit instructions. Depending on whether you want images embedded in specific cells or just listed as file paths, the required VBA functions (like Pictures.Insert or FileSystemObject) will change drastically.

Specify Picture Placement within Excel

Decide if the images will be inserted into individual cells (which requires VBA to calculate and match cell Width/Height properties) or if they will float across the worksheet as free-standing objects.

Validate Target Folder Contents

Check your source directories to confirm whether each subfolder contains exactly one picture or multiple pictures. This dictates whether your macro needs a nested loop to iterate through multiple files per folder.

Define the Sorting Sequence

Establish the order in which photos should appear in Excel. Decide if the macro should sort by alphabetical folder name, date modified, or a custom list sequence before pasting.

Determine File Path and Storage Handling

Decide the end goal of the macro: whether you want to embed the actual image files into the Excel document, just list the organized file paths in a text column, or use Excel to copy the original files into a new, consolidated master folder.

Using FileSystemObject: Once these rules are defined, the most robust way to build this macro is by referencing the 'Microsoft Scripting Runtime' library to use FileSystemObject (FSO) for iterating through folders.

Advanced Macro Support

Automate Image Insertion with WPS Spreadsheet Macros

WPS Office fully supports VBA (Visual Basic for Applications), allowing you to seamlessly run complex macros for automating tasks like bulk image insertion from multiple folders directly into your spreadsheet.

1. Enable Developer Tools: Open your workbook in WPS Spreadsheet, navigate to the top ribbon, and click on the 'Developer' tab.
2. Open Visual Basic Editor: Click the 'Visual Basic' or 'VB Editor' icon to open the macro coding environment.
3. Insert Your Script: Right-click on your VBAProject, select 'Insert', and choose 'Module' to paste your picture-arranging VBA code.
4. Run the Macro: Close the editor, click on 'Macros' in the Developer tab, select your newly created script, and click 'Run' to pull the images from your folders.

100% compatible with Microsoft Excel VBA scripts and macrosInsert, align, and resize multiple images smoothlyFree, lightweight, and fast spreadsheet alternativeBuilt-in robust Visual Basic Editor interface

Frequently Asked Questions

How do I loop through multiple folders using VBA?

You can use the FileSystemObject (FSO) library or the built-in Dir function with the vbDirectory attribute to programmatically iterate through subfolders and locate your image files.

Can I make the inserted pictures fit exactly inside Excel cells?

Yes. After using the Pictures.Insert method in VBA, you can set the resulting Shape object's Top, Left, Width, and Height properties to equal the corresponding properties of your target Range.

Why does my VBA script show a 'File Not Found' error when inserting images?

This error typically occurs if the folder path string is formatted incorrectly (e.g., missing a trailing backslash) or if the image name specified in the code doesn't exactly match the file in the directory. You can use Debug.Print to verify the exact file path string before execution.

Does WPS Spreadsheet support executing Excel VBA macros?

Yes, WPS Office provides excellent compatibility with Excel macros, allowing you to run standard VBA scripts for automating data entry, formatting, and file imports without modifying the code.

Excel & Data Problems