VBA & Macro Problems

How to Create Folders Using Array Names in Excel VBA

John Wilson Oct 10, 2026 870 views

Question details

The user needs to create subfolders automatically based on a specific list of names stored in a VBA array.

How to Create Folders Using Array Names in Excel VBA

Product: Excel VBA / WPS Spreadsheet Macros
Device & OS: not provided
Scenario: Automating the creation of multiple directories or project folders using an array of names.
Observed behavior: Folders are successfully created with intended names corresponding to each element within the array.

Before you start

Ensure you have Developer tools enabled in your spreadsheet application and that you have basic familiarity with writing loops in VBA.

Solution 1Recommended

Reference Array Elements Directly Inside a Loop

The most direct way to create folders from an array is to iterate through it and pass each individual element to the folder Add method.

When looping through an array to name new folders, you must pass the specific index value (the item in the array) instead of passing the entire array object itself.

Open the VBA Editor

Press ALT + F11 to open the Visual Basic for Applications (VBA) Editor.

Define Your Array

Set up your array containing the desired folder names, for example: Dim arrSubProj() As Variant.

Create a Loop

Construct a For loop to iterate through the array: For I = 1 To UBound(arrSubProj).

Use the Add Method

Inside the loop, use the Add method referencing the array element directly by its index: Set mySubFolder = myFolders.Add(arrSubProj(I)).

Reference Array Elements Directly Inside a Loop

Debugging Tip: You can insert the command 'Debug.Print arrSubProj(I)' below your For statement to verify the exact values being passed in the Immediate Window.

Solution 2

Conditional Folder Creation for Specific Projects

Use this method if you only need to generate subfolders when specific conditions are met on a user form.

Automate with WPS Office

Run Macros and Create Folders Seamlessly in WPS Office

WPS Office provides robust support for VBA and macros, allowing you to automate repetitive tasks like folder creation with ease. You can write, edit, and run your VBA arrays directly within the built-in visual editor.

1. Open Your Spreadsheet: Launch WPS Spreadsheet and open your working document.
2. Access Developer Tools: Navigate to the 'Developer' tab on the top ribbon.
3. Launch the VBA Editor: Click 'Visual Basic' to open the built-in VBA Editor.
4. Insert Your Code: Insert a new module and paste your folder creation array loop script.
5. Run the Macro: Press F5 or click 'Run' to execute your code and generate the designated folders.

Fully compatible with Microsoft Excel VBA scripts and macrosBuilt-in VBA editor with powerful debugging and array inspection toolsEasily automate folder and file management tasks directly from your spreadsheetFree to use with comprehensive spreadsheet capabilities and familiar UI

microsoft office alternative - wps office

Frequently Asked Questions

Why am I getting a 'Type Mismatch' error when running my folder creation macro?

This usually happens if you try to pass the entire array variable instead of an individual element. Ensure you include the loop index, such as arrSubProj(I), inside your folder Add method.

How do I check what string values are being passed from the array?

Add the line 'Debug.Print arrSubProj(I)' inside your loop right before your Add method. You can view these output values by opening the Immediate Window (Ctrl + G) in the VBA Editor to confirm they are valid folder names.

Can I use an array to create nested subfolders?

Yes. You can use the FileSystemObject (FSO) in VBA to specify full paths. Ensure your array contains the desired subfolder names and concatenate them to your base path string during the loop.

Excel & Data Problems