Documentation

File Processing Wizard

What does it do?

The File Processing Wizard generates code that loops through all files in a folder (and optionally all subfolders) and performs actions on the files that meet certain criteria. Most commonly this is used to open up all files in a folder or merge all files in a folder.

file processing wizard

How to Access it?

file builder

File Processing Wizard Walkthrough

Loop Through Files

The File Processing Wizard creates a "Do While" Loop that loops through each file in a folder. It's very intuitive to use, but we will walkthrough how the code actually works. After removing the declarations and trimming down some of the other code, the Loop portion of the code looks like this:

'Set Path     
strPath = "D:\Dropbox\" 'Path must end in \

'File Criteria: All Excel + CSV Files
strFileCriteria = "*.*"
strFileName = Dir(strPath + strFileCriteria)

'Loop Through Files in Directoy
Do While strFileName <> ""

Loop

First it defines the folder path:

`strPath = "D:\Dropbox\" 'Path must end in \`

The character: ***** is a wildcard that represents any number of any characters. Here it is used to select all files.

`strFileCriteria = "*.*"`

Define the file path and directory:

`strFileName = Dir(strPath + strFileCriteria)`

This loops through all the files in the directory:

`Do While strFileName <> ""

Loop`

Basic Information

vba file builder 1

  1. Enter name for Sub Procedure
  2. Type the folder path or use the button to navigate to your desired folder.
  3. Include Subfolders? When checked, VBA will iterate through all files in the folder and in all subfolers.
  4. Choose which type of files to loop through 'All Files', 'Excel Files', or 'Excel Files + CSV'

Criteria

Enter the criteria used to determine which files are processed:

vba file builder 2 criteria

Leave this section blank to select all files. Otherwise enter your desired file name criteria. Remember you can use wilcards: ?, or * to make smarter selections. Add additional criteria by clicking the checkboxes next to the 2nd and 3rd criteria.

Exclude ThisWorkbook - Ignores the workbook where this code is stored. Generally, this should be checked.

Ignore Case? - When checked, case (upper or lower) is ignored when determining of a file name matches the criteria. Most users will want to keep this checked.

Action

Select the action to be performed:

vba file builder 3 action

Choose from list, delete, move, copy, open, or merge the files.

Last modified 4 years ago.

^ Top