• File Processing Wizard
• What does it do?
• How to Access it?
• File Processing Wizard Walkthrough
• Loop Through Files
• Basic Information
• Criteria
• Action

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.

How to Access it?

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

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:

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:

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

• List Files - Creates a list of the files.
• Delete, Move, and Copy Files - For Move and Copy, a destination folder must be specified.
• Open Files - Opens all the files. Choose what to do after a specified file is processed: Keep Open, Close without saving, Close with saving. Note: you may want to specify specific additional actions to perform after opening the file. You will need to do this manually within the final generated code.
• Merge Files - A simple merge files process. This will copy the sheet contents from the specified sheet from each file into the destination sheet in This Workbook. This is great for quickly merging together CSV files. Similar to the Open Files option, you will need to specify what to do after the file has been opened.

Last modified 3 years ago.