Tag Archives: directory

application filedialog featured

VBA FileDialog – Opening, Selecting and Saving files and folders

1 Star2 Stars3 Stars4 Stars5 Stars (5 votes, average: 4.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 csv files

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

1 Star2 Stars3 Stars4 Stars5 Stars (2 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 (4 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