Tag Archives: file

application filedialog featured

VBA FileDialog – Opening, Selecting and Saving files and folders

1 Star2 Stars3 Stars4 Stars5 Stars (2 votes, average: 5.00 out of 5)
Loading...

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.

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

select file filedialogThe 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:\"
'Optional: Add filters
fDialog.Filters.Clear
fDialog.Filters.Add "Excel files", "*.xlsx"
fDialog.Filters.Add "All files", "*.*"

'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:\"
'Optional: Add filters
fDialog.Filters.Clear
fDialog.Filters.Add "Excel files", "*.xlsx"
fDialog.Filters.Add "All files", "*.*"

'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

select folder application.filedialogSelecting 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

file open application.filedialogOpening 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:\"
    
'Optional: Add filters
fDialog.Filters.Clear
fDialog.Filters.Add "All files", "*.*"
fDialog.Filters.Add "Excel files", "*.xlsx"
  
If fDialog.Show = -1 Then
  Debug.Print fDialog.SelectedItems(1)
End If

Save file – msoFileDialogSaveAs

saveas application.filedialogSaving 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)
'...
'Optional: Add filters
fDialog.Filters.Clear
fDialog.Filters.Add "All files", "*.*"
fDialog.Filters.Add "Excel files", "*.xlsx;*.xls;*.xlsm"
fDialog.Filters.Add "Text/CSV files", "*.txt;*.csv"
'...

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

1 Star2 Stars3 Stars4 Stars5 Stars (1 votes, average: 5.00 out of 5)
Loading...

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

Merge Source.xlsx with Destination.xlsx
Merge Source.xlsx with Destination.xlsx
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

Copy each Worksheet to the destination Workbook
Copy each Worksheet to the destination Workbook
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

Copy Worksheet to destination Workbook
Copy Worksheet to destination Workbook

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

Merge Excel files using VBA

merge excel filesLet 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
    Set destWb = excelApp.Workbooks.Add
    
    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.

Download the code

Want to download the code above and the example?

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

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

1 Star2 Stars3 Stars4 Stars5 Stars (1 votes, average: 5.00 out of 5)
Loading...

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 fileCSV (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:

Sample CSV (Comma-separated values file)
Sample CSV (Comma-separated values 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:

CSV file opened in Excel
CSV file opened in Excel

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:

Data ribbon: Text to Columns
Data ribbon: Text to Columns

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.

Text to Columns Wizard: Step 1
Text to Columns Wizard: Step 1

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

Text to Columns Wizard: Step 2
Text to Columns Wizard: Step 2

Format your columns (optional)

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:

Text to Columns Wizard: Step 3
Text to Columns Wizard: Step 3

Admire your Excel converted CSV file

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

Convert CSV to Excel file
Convert CSV to Excel file

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

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
    .FillAdjacentFormulas = False
    .PreserveFormatting = True
    .RefreshOnFileOpen = False
    .RefreshStyle = xlInsertDeleteCells
    .SaveData = True
    .AdjustColumnWidth = 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
    .TextFileCommaDelimiter = True
    .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
 
Set rs = CreateObject("ADODB.Recordset")
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

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

1 Star2 Stars3 Stars4 Stars5 Stars (1 votes, average: 5.00 out of 5)
Loading...

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.
merge CSV files Windows CMD

Open CMD command line within folder

Click on the filepath of the Windows Explorer window and type cmd and hit ENTER.
merge CSV files Windows CMD

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

merge CSV files Windows CMD
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
    firstHeader = True
    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
        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
        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

headers
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)

addNewLine
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
    firstHeader = True
    Dim col As Collection
    Set col = New Collection
    col.Add folderName
    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
            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
            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

headers
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)

addNewLine
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

VBA Dir function – How to traverse directories?

1 Star2 Stars3 Stars4 Stars5 Stars (3 votes, average: 5.00 out of 5)
Loading...

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 VBA Dir function syntax
The VBA Dir function syntax

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:

VBA Dir Example Directory
VBA Dir Example 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)!
VBA Dir Example1

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:
VBA Dir Example 2

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:
VBA Dir 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
            dirCollection.Add currentPath
        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:
VBA Dir Example 3