VBA FileDialog – Opening, Selecting and Saving files and folders

(7 votes, average: 4.29 out of 5)

Often in VBA we need to ask the users to select files or directories before we execute the actual functionality of our macro. Welcome to the VBA Open file dialog post. Today we will learn how to use the Application.FileDialog, to understand the various msoFileDialogFilePicker file dialog picking options and how to properly manage these dialogs.

VBA Dialogs

Application.FileDialog function

Before we start let’s understand the Application.FileDialog function.

Syntax

The Application.FileDialog has the following syntax:

Application.FileDialog( fileDialogType as MsoFileDialogType )


Parameter

MsoFileDialogType
An enumeration defining the type of file dialog to open. It has the following values:

Value Description
msoFileDialogOpen Open dialog box
msoFileDialogSaveAs Save As dialog box
msoFileDialogFilePicker File picker dialog box
msoFileDialogFolderPicker Folder picker dialog box

Application.FileDialog properties and functions

FileDialog properties

Property Description
AllowMultiSelect Allow to select more than one file or folder
ButtonName Text displayed on the action button of a file dialog box
DialogType Change the MsoFileDialogType (see above)
Filter Set a file filter to filter file types user can select
InitialFileName The initial path to be opened e.g. C:\
InitialView The initial file view. Can be one of the following:

Value
msoFileDialogViewDetails
msoFileDialogViewLargeIcons
msoFileDialogViewList
msoFileDialogViewPreview
msoFileDialogViewProperties
msoFileDialogViewSmallIcons
msoFileDialogViewThumbnail
msoFileDialogViewWebView
SelectedItems Collection of type FileDialogSelectedItems with all selected items
Title Title of the Open file dialog window

FileDialog Show

The Application.FileDialog has two functions Show and Execute. In practice you will only need the Show function. What does the Show function do?

The Show function shows the FileDialog allowing to select files and folders. Once the FileDialog is closed (Cancel or Ok). The possible results are:

• -1 – user pressed Ok
• 0 – user pressed X or Cancel

Select files – msoFileDialogFilePicker

The msoFileDialogFilePicker dialog type allows you to select one or more files.

Select single files

The most common select file scenario is asking the user to select a single file. The code below does just that:

Dim fDialog As FileDialog, result As Integer
Set fDialog = Application.FileDialog(msoFileDialogFilePicker)

'Optional: FileDialog properties
fDialog.AllowMultiSelect = False
fDialog.title = "Select a file"
fDialog.InitialFileName = "C:\"
fDialog.Filters.Clear

'Show the dialog. -1 means success!
If fDialog.Show = -1 Then
Debug.Print fDialog.SelectedItems(1)
End If


The result can look similar to this:

C:\somefile.xlsx


Select multiple files

Quite common is a scenario when you are asking the user to select one or more files. The code below does just that. Notice that you need to set AllowMultiSelect to True.

Dim fDialog As FileDialog, result As Integer
Set fDialog = Application.FileDialog(msoFileDialogFilePicker)

'IMPORTANT!
fDialog.AllowMultiSelect = True

'Optional FileDialog properties
fDialog.title = "Select a file"
fDialog.InitialFileName = "C:\"
fDialog.Filters.Clear

'Show the dialog. -1 means success!
If fDialog.Show = -1 Then
For Each it In fDialog.SelectedItems
Debug.Print it
Next it
End If


The result can look similar to this:

C:\somefile.xlsx
C:\somefile1.xlsx
C:\somefile2.xlsx


Select folder – msoFileDialogFilePicker

Selecting a folder is more simple than selecting files. However only a single folder can be select within a single dialog window.

Select folder example

The dialog below will ask the user to select a folder:

Set fDialog = Application.FileDialog(msoFileDialogFolderPicker)

'Optional: FileDialog properties
fDialog.title = "Select a folder"
fDialog.InitialFileName = "C:\"

If fDialog.Show = -1 Then
Debug.Print fDialog.SelectedItems(1)
End If

The msoFileDialogFolderPicker dialog allows you to only select a SINGLE folder and obviously does not support file filders

Open file – msoFileDialogOpen

Opening files is much more simple as it usually involves a single file. The only difference between the behavior between Selecting and Opening files are button labels.

The open file dialog will in fact not open any files! It will just allow the user to select files to open. You need to open the files for reading / writing yourself. Check out my posts:

Open file example

The dialog below will ask the user to select a file to open:

Dim fDialog As FileDialog, result As Integer, it As Variant
Set fDialog = Application.FileDialog(msoFileDialogOpen)

'Optional: FileDialog properties
fDialog.title = "Select a file"
fDialog.InitialFileName = "C:\"

fDialog.Filters.Clear

If fDialog.Show = -1 Then
Debug.Print fDialog.SelectedItems(1)
End If


Save file – msoFileDialogSaveAs

Saving a file is similarly easy, and also only the buttons are differently named.

The save file dialog will in fact not save any files! It will just allow the user to select a filename for the file. You need to open the files for reading / writing yourself. Check out my post on how to write files in VBA

Save file example

The dialog below will ask the user to select a path to which a files is to be saved:

Dim fDialog As FileDialog, result As Integer, it As Variant
Set fDialog = Application.FileDialog(msoFileDialogSaveAs)

'Optional: FileDialog properties
fDialog.title = "Save a file"
fDialog.InitialFileName = "C:\"

If fDialog.Show = -1 Then
Debug.Print fDialog.SelectedItems(1)
End If

The msoFileDialogSaveAs dialog does NOT support file filters

FileDialog Filters

One of the common problems with working with the Application.FileDialog is setting multiple file filters. Below some common examples of how to do this properly. To add a filter for multiple files use the semicolor ;:

Dim fDialog As FileDialog
Set fDialog = Application.FileDialog(msoFileDialogOpen)
'...
fDialog.Filters.Clear
'...


Be sure to clear your list of filters each time. The FileDialog has its nuisances and often filters are not cleared automatically. Hence, when creating multiple dialogs you might see filters coming from previous executed dialogs if not cleared and re-initiated properly.

Merge Excel files – How to merge multiple Excel files

(4 votes, average: 3.75 out of 5)

Having your data spread across multiple Excel files? Want to merge Excel files into a single Excel Workbook? Today we going to explore just that – how to merge multiple Excel Workbook spreadsheets into a single Workbook. The obvious tool for this task is Visual Basic for Applications so let us jump right in.

Merge Excel files manually

Open the destination Workbook

Open the destination Excel Workbook (in our example Destination.xlsx) to which you want to copy the Worksheets of the remaining Workbooks (in our example Source.xlsx).

The further steps need to be repeated for each Excel Workbook you want to copy to the destination Workbook.

Open (each) the Workbook you want to merge and copy Worksheets

Open each Excel Workbook you want to merge with the destination Workbook.

Next right-click on each Worksheet you want to copy, click Move or Copy.... In the Move or Copy Window select the destination Workbook (Destination.xlsx in our case). Next:

• If you want to move (cut & paste) the Worksheet – simply click the OK button to proceed
• If you want to copy (copy & paste) the Worksheet – select the Create a Copy checkbox and click the OK button

Repeat Step 2 for each Workbook you want to copy to the destination Workbook.

Merge Excel files using VBA

Let us assume we have a couple of files listed in our directory (in my example these are File1 and File2.xlsx). What we want to do is create a new Excel Workbook and copy Worksheets to this new Workbook. I wanted however to account for 2 typical scenarios:

• Copy ALL worksheets
• Copy only a single worksheet – with a specific name

The code below supports both these scenarios.

Merge Excel files code

Use the MergeExcelFiles Sub procedure below to merge any number of Workbooks:

Sub MergeExcelFiles(fileNames() As String, Optional worksheetName As String = vbNullString, Optional mergedFileName As String = "merged.xlsx")
Dim fileName As Variant, wb As Workbook, ws As Worksheet, destWb As Workbook, excelApp As Application
Set excelApp = New Application

For Each fileName In fileNames
Set wb = excelApp.Workbooks.Open(fileName, ReadOnly = True)
For Each ws In wb.Sheets
If worksheetName <> vbNullString Then
If ws.Name = worksheetName Then ws.Copy After:=destWb.Sheets(destWb.Sheets.Count)
Else
ws.Copy After:=destWb.Sheets(destWb.Sheets.Count)
End If
Next ws
wb.Close SaveChanges:=False
Next fileName

destWb.SaveAs ThisWorkbook.Path & "\" & mergedFileName
destWb.Close SaveChanges:=False
excelApp.Quit
Set destWb = Nothing: Set excelApp = Nothing
MsgBox "Merge completed!"
End Sub


How to use the procedure above? Below I create a simple Test procedure that lists the Excel files within the Workbook directory and merges the Workbooks.

Sub TestMerge()
Dim fileNames(0 To 1) As String
fileNames(0) = ThisWorkbook.Path & "\File1.xlsx"
fileNames(1) = ThisWorkbook.Path & "\File2.xlsx"
'...

'Merge all worksheets in listed files
MergeExcelFiles fileNames

'Merge only worksheets named "SomeWs" in listed files and save the merged file as "test.xlsx"
MergeExcelFiles fileNames, "SomeWs", "test.xlsx"

End Sub


Pretty simple right?

Merge Excel files within a directory

The scenario above works pretty well for situations where we want to list explicitly files we want to merge into a single Excel Workbook. How about when we have tons of files? Or to make it more simple, if we want to merge all files within a SINGLE directory. As this is also a typical scenario I have modified the above Test procedure to accommodate just that:

Sub TestMergeDirectory()
Dim fileNames() As String, currIndex As Long, fileName As String, directory As String

directory = ThisWorkbook.Path & "\SomeDir\"
ReDim fileNames(0 To 0) As String
fileName = Dir(directory)
fileNames(0) = directory & fileName
Do Until fileName = vbNullString
currIndex = currIndex + 1
ReDim Preserve fileNames(0 To currIndex) As String
fileName = Dir
fileNames(currIndex) = directory & fileName
Loop
ReDim Preserve fileNames(0 To currIndex - 1) As String

MergeExcelFiles fileNames
End Sub


What happens is that we use the VBA Dir function to loop through all files within a selected directory. As we go along we modify our VBA Array redefining its size and adding additional items.

Merge Excel files in a whole directory structure

One last scenario that came to mind is when we have a whole directory structure (directories within other directories) containing files with want to merge. As the VBA Dir function works only for a single directory and does not traverse any directories within we would need to amend additionally the code above. If needed I recommend reading my post on How to traverse directories using the VBA Dir function.

Convert CSV to Excel – How to open CSV and save as Excel

(1 votes, average: 5.00 out of 5)

CSV files are commonplace nowadays. Hence knowing how to convert CSV to Excel is a useful skill. CSV files are especially useful when wanting to save a table worth of data in a simple to read format. CSVs are also easy files to create and read from – as they are basically text files with a .csv file extension. That means they easily open in Wordpad, Notepad, Word and virtually any other text editor.

CSV (Comma-separated values) – in computing these are text files that store tabular data (numbers and text in columns):

• Each row represent a single record
• Each column represents a certain property
• Each “cell“, or in other words columns within a row, are separated by a certain delimiter. Usually a comma , character (but not only!)
• Usually the first row is the header – contains names for each column

Today I want to show how you can quickly convert a CSV file into an Excel XLSX file. I will base this tutorial on the following example of a CSV file:

Open the file in Excel

Simply double click on the file or if needed right-click and Open with the file in Microsoft Excel. The file should open showing only a single Worksheet like shown below:

If columns in a CSV are separated by your default Windows list separator the columns will be separated automatically without needing to proceed further. Where to find you default Windows locale list separator? Control Panel->Region and Language->Additional Settings->List Separator. In my case it was a ;

Select first column and proceed to Text to Columns

Select the entire first column where all your data should be located. Next click on the Text to Columns button in the DATA ribbon tab:

Proceed according to Wizard instructions

This is the hard part. Text to Columns need additional information on the delimiter and format of your columns.

Delimited or Fixed width?

CSV files are usually delimited using a specific character (like a comma or semicolon), in this case select Delimited. Sometimes however columns are fixed width, separated by spaces, in this case select Fixed width. Next click Next to proceed.

Select delimiter

Assuming your columns are separated with a specific delimiter you need to provide this delimiter in the Wizard. Look at the Data preview to make sure your columns will be separated correctly. When finished proceed with Next

The last step is to format your columns if needed. If your columns represent Dates or you want to pull a column containing numbers/dates as text instead – be sure to format it appropriately. Usually, however, you are fine with hitting Finish:

If you have proceeded according to the steps above you should have a neatly formatted spreadsheet like the one below.

One last thing as Steve Jobs used to say… Remember to save the file as an Excel XSLX (or XLSB or similar) file:

Convert multiple CSV to Excel with VBA

If you have multiple CSVs you want to convert or pull into an Excel file the above approach may be a big burden. So lets use some VBA macros to help.

Import entire CSV

The below is take straight from my Read file in VBA blog post. This pulls a single file into the destRng Excel range.

Dim ws as Worksheet, destRng as Range, fileName as String
Set destRng = Range("A1")
Set ws = ActiveSheet
fileName = "C:\text.csv" 'Replace with file name
With ws.QueryTables.Add(Connection:= "TEXT;" & fileName & "", Destination:=destRng)
.FieldNames = True
.RowNumbers = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
.SaveData = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = 852
.TextFileStartRow = 1
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierDoubleQuote
'Select your delimiter - selected below for Comma
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = False
.TextFileSemicolonDelimiter = False
.TextFileSpaceDelimiter = False
.TextFileTrailingMinusNumbers = True
'This will refresh the query
End With


Import selected CSV rows

Now a more interesting and complex scenario – let us assume we want to import just some rows of our CSV. Fortunately Excel (as well as Access) support SQL queries. We can therefore do a simple SELECT query to upload all records or add some filtering (using WHERE clause), grouping (using GROUP BY clause) and etc.

'Assuming file looks like this. File path: C:\test.csv
'"Col1", "Col2", "Col3"
'1     , 2     , 3
'11    , 12    , 1
'2     , 5     , 6

strcon = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\;" _
& "Extended Properties=""text;HDR=Yes;FMT=Delimited"";"
strSQL = "SELECT * FROM test.csv WHERE Col1 > 10"
rs.Open strSQL, strcon, 3, 3
rs.MoveFirst
Do
col1 = rs("Col1")
col2 = rs("Col2")
col3 = rs("Col3")
rs.MoveNext
Loop Until rs.EOF


The above will only pull the second row as Col1 is > 10.

Convert CSV to Excel tips

CSV files are usually used when a file contains a large amount of data. Excel tends to bloat pretty quickly although it compresses the data pretty well. Nevertheless you might find yourself struggling with Excel performance or even experiencing an Excel crash. What to do? Save the file as an XLSB – read more here.

Merge CSV files or TXT files in a folder – using Excel or CMD

(2 votes, average: 5.00 out of 5)

Often we face the challenge of having to merge csv files or txt files in a folder, into a single file. Excel is the obvious tool for such tasks and today I will show a couple of easy ways for merging multiple files, in a single or even a whole structure of folders, into a single CSV or text file. To merge csv files or other text files it is often best to use Visual Basic for Applications in Excel.

Let’s start with the simplest approach using Windows Command line without having to use Excel.

Merge CSV files using Windows CMD

This approach uses the Windows Command line Copy command.

Open the folder which should contain your CSV or TXT files

Open in Windows Explorer the folder containing CSV or TXT files to be merged. These should be without headers or only the first file should be with headers.

Open CMD command line within folder

Click on the filepath of the Windows Explorer window and type cmd and hit ENTER.

Merge the files using COPY command

The CMD Windows command line Window should open. Type the following command and hit ENTER to merge files

copy *.csv merge.csv


The result will be the newly created merge.csv file with merged data across all CSV files within the directory.

Simply replace *.csv with *.txt to merge text files instead of CSV files

Merge list of csv, txt files

The previous method was very simple and didn’t require the use of Excel or MS Office. The below and the following approaches will provided you with more flexibility when merging files. If you don’t know how to use Macros in Excel read my Tutorial first.

Assuming you want to merge a list of files in a String Array you can use the procedure below. It will merge all provided csv or text files into a single new text file.

Sub MergeFiles(fileNames() As String, newFileName As String, Optional headers As Boolean = True, Optional addNewLine As Boolean = False)
Dim fileName As Variant, textData As String, fileNo As Integer, result As String, firstHeader As Boolean
For Each fileName In fileNames
fileNo = FreeFile
Open fileName For Input As #fileNo
textData = Input$(LOF(fileNo), fileNo) Close #fileNo If headers Then result = result & IIf(addNewLine, vbNewLine, "") & IIf(firstHeader, textData, Right(textData, Len(textData) - InStr(textData, vbNewLine))) firstHeader = False Else result = result & IIf(addNewLine, vbNewLine, "") & textData End If Next fileName fileNo = FreeFile Open newFileName For Output As #fileNo Print #fileNo, result Close #fileNo End Sub  Merge csv, txt files example See an example below of how to use the MergeFiles procedure: Dim fileNames(0 To 1) As String fileNames(0) = "C:\somefolder\test.csv" fileNames(1) = "C:\somefolder\test1.csv" MergeFiles fileNames, "C:\Merged.csv", True, False  MergeFiles Parameters fileNames() Array of Strings representing full file paths to files that are to be merged newFileName The name of the new merged file that is to be created headers Optional. True by default. This is meant for CSV TXT files (HDR). If True assumes that all files have headers (first row with columns). Only first header will be merged into the new file (newFileName) addNewLine Optional. False by default. If True a new line (vbNewLine) character will be added between each merged file Merge csv, txt files within specified folder Another case is when you want to merge all csv files within a single folder. This procedure is similar to the previous one with the exception that it runs through all files within a single directory (excluding subdirectories – for that scroll to next procedure). You can also use wildcards such as “*.csv” to be sure that only csv files are merged a not other files – read my post on the VBA Dir function to learn more. Sub MergeFilesInFolder(folderName As String, newFileName As String, Optional headers As Boolean = True, Optional addNewLine As Boolean = False) Dim fileName As Variant, textData As String, fileNo As Integer, result As String, firstHeader As Boolean firstHeader = True fileName = Dir(folderName) Do Until fileName = "" fileNo = FreeFile Open (Left(folderName, InStrRev(folderName, "\")) & fileName) For Input As #fileNo textData = Input$(LOF(fileNo), fileNo)
Close #fileNo
result = result & IIf(addNewLine, vbNewLine, "") & IIf(firstHeader, textData, Right(textData, Len(textData) - InStr(textData, vbNewLine)))
Else
result = result & IIf(addNewLine, vbNewLine, "") & textData
End If
fileName = Dir
Loop
fileNo = FreeFile
Open newFileName For Output As #fileNo
Print #fileNo, result
Close #fileNo
End Sub


Merge csv, txt files in folder example

See an example below of how to use the MergeFilesInFolder procedure:

MergeFilesInFolder "C:\somefolder\*.csv", "C:\MergedFolder.csv", True, False


MergeFilesInFolder Parameters

folderName
A folder including all files to be merged. Wildcards are permitted if supported by the VBA Dir function

newFileName
The name of the new merged file that is to be created

Optional. True by default. This is meant for csv files (HDR). If True assumes that all files have headers (first row with columns). Only first header will be merged into the new file (newFileName)

Optional. False by default. If True a new line (vbNewLine) character will be added between each merged file

Merge csv, txt files within all subfolders

The most complex case is when you want to merge files not only within a certain directory but also within all subdirectories. This will equally work for a scenario when there are no subfolders.

Sub MergeFilesInSubFolders(folderName As String, pattern As String, newFileName As String, Optional headers As Boolean = True, Optional addNewLine As Boolean = False)
Dim fileName As Variant, folder As Variant, textData As String, fileNo As Integer, result As String, firstHeader As Boolean
Dim col As Collection
Set col = New Collection
TraversePath folderName, col
For Each folder In col
fileName = Dir(folder & pattern)
Do Until fileName = ""
fileNo = FreeFile
Open (Left(folder, InStrRev(folder, "\")) & fileName) For Input As #fileNo
textData = Input\$(LOF(fileNo), fileNo)
Close #fileNo
result = result & IIf(addNewLine, vbNewLine, "") & IIf(firstHeader, textData, Right(textData, Len(textData) - InStr(textData, vbNewLine)))
Else
result = result & IIf(addNewLine, vbNewLine, "") & textData
End If
fileName = Dir
Loop
Next folder
fileNo = FreeFile
Open newFileName For Output As #fileNo
Print #fileNo, result
Close #fileNo
End Sub

Function TraversePath(path As Variant, allDirCollection As Collection)
Dim currentPath As String, directory As Variant
Dim dirCollection As Collection
Set dirCollection = New Collection

currentPath = Dir(path, vbDirectory)
'Explore current directory
Do Until currentPath = vbNullString
If Left(currentPath, 1) <> "." And Left(currentPath, 2) <> ".." And _
(GetAttr(path & currentPath) And vbDirectory) = vbDirectory Then
dirCollection.Add path & currentPath & "\"
allDirCollection.Add path & currentPath & "\"
End If
currentPath = Dir()
Loop

'Explore subsequent directories
For Each directory In dirCollection
TraversePath directory, allDirCollection
Next directory
End Function

Read this post to learn more on using the VBA Dir function to traverse directories and subdirectories

Merge csv, txt files within subfolders example

See an example below of how to use the MergeFilesInSubFoldersprocedure:

MergeFilesInSubFolders "C:\somefolder\", "C:\MergedSubFolders.csv", True, False


MergeFilesInSubFolders Parameters

folderName
A folder with or without subfolders including all files to be merged. Use wildcards with pattern parameter

pattern
If needed a pattern using wildcards permitted by the VBA Dir function

newFileName
The name of the new merged file that is to be created

Optional. True by default. This is meant for csv files (HDR). If True assumes that all files have headers (first row with columns). Only first header will be merged into the new file (newFileName)

Optional. False by default. If True a new line (vbNewLine) character will be added between each merged file

Merge CSV files – filter records

Sometimes we want to download just a subset of records in our CSV files. One way is uploading the data and then filtering it in Excel. But why not do it in one go? See my SQL AddIn or my read CSV file using SQL example in this post here.

VBA Dir function – How to traverse directories?

(4 votes, average: 5.00 out of 5)

The VBA Dir function is crucial to exploring any file system with the use of VBA – whether in Excel or other MS Office applications. In some case true you can resort to a Select File Dialog, however, in other cases you need to be able to easily match files or directories in file systems in VBA. Today, let us learn how to use the Dir function.

Go here if you are looking for the VBA Dir function reference page

VBA Dir Syntax

The Excel Dir function returns the first filename (directory) that matches the path and the provided attributes. To retrieve subsequent filenames (directories) that match the path and attributes call the Dir function again without providing ANY arguments.

The Dir function by default DOES NOT TRAVERSE the entire directory structure provided by the given path. It will match filenames (directories) matching the path and attributes within only the given path. Read on to learn how to use Dir to traverse the whole directory structure.

VBA Dir example

Let’s start with a simple example before we jump into more advanced examples. Let’s see how to explore the C:\Root\ directory:

Dim path As String, currentPath As String
path = "C:\Root\"

currentPath = Dir(path)
Do Until currentPath = vbNullString
Debug.Print currentPath
currentPath = Dir()
Loop


Notice that I highlighted on purpose both calls to the Dir function. The first Dir(path) call will return the first filename within the C:\Root\ directory. Any subsequent Dir() (notice no arguments) calls will return next files under the directory. Also as I have not provided any attributes the Dir function will only match against vbNormal (normal files)!

Using attributes

Assuming the same directory structure as in the previous example let’s change the default vbNormal attribute to vbDirectory. This will allow us to explore not only file matched under the given path but also the directories.

Dim path As String, currentPath As String
path = "C:\Root\"

currentPath = Dir(path, vbDirectory)
Do Until currentPath = vbNullString
Debug.Print currentPath
currentPath = Dir()
Loop


Notice that now we should be seeing, aside from our file, also directories being listed. The output:

Similarly you can use the other VBA Dir attributes to search for hidden, system files, aliases and volumes. Most attributes include normal files so be aware the the files listed WON’T BE LIMITED to just e.g. hidden files.

Using wildcards

The VBA Dir function works great also with wild cards:

• * – multiple character wildcard, including zero-length
• ? – single character wildcard

Some common examples:

Dir("C:\HelloWorld.txt")
'Matches: HelloWorld.txt

Dir("C:\Hello*World.txt")
'Matches: HelloWorld.txt, HelloThereWorld.txt, Hello_World.txt etc.

Dir("C:\?elloWorld.txt")
'Matches: HelloWorld.txt, MelloWorld.txt, YelloWorld.txt etc.


Traversing directories

The VBA Dir function unfortunately by default does not traverse the entire directory, exploring subfolders. In the examples we were limited to just listing the files and folders of the C:\Root directory:

A simple recursive VBA Dir implementation won’t do either as the Dir function is reset whenever it is called globally with any arguments. Fortunately, we can use a native VBA object called the VBA Collection. We will stack all subsequent directories onto our Collection and then explore them recursively. The TraversePath function will do just that:

Sub TraversePath(path As String)
Dim currentPath As String, directory As Variant
Dim dirCollection As Collection
Set dirCollection = New Collection

currentPath = Dir(path, vbDirectory)

'Explore current directory
Do Until currentPath = vbNullString
Debug.Print currentPath
If Left(currentPath, 1) <> "." And _
(GetAttr(path & currentPath) And vbDirectory) = vbDirectory Then
End If
currentPath = Dir()
Loop

'Explore subsequent directories
For Each directory In dirCollection
Debug.Print "---SubDirectory: " & directory & "---"
TraversePath path & directory & "\"
Next directory
End Sub


Let’s try it out on our C:\Root directory example:

Sub Test()
TraversePath "C:\Root\"
End Sub


The TraversePath should not traverse all directories within our Root directory and list any normal files and folders: