VBA is a language that can be used not only with the Microsoft Office applications as Excel but also with the Windows operating system.
This article presents several useful VBA functions which use the windows explorer (default folder explorer in Windows).
These procedures which interact with the windows explorer will work only for windows users. The windows explorer tool is used often by VBA to load data files which can take different extensions (.xlsx, .xml, .csv…).
It is easy to attach your macro to your design button.
The FileDialog object allow you to communicate with the windows explorer.
Design an interface for your VBA macro
It is important, especially when developing a macro for external users, to pay attention to its design. Obviously the heart of the work will be in the code, but for your users, your tool will have to be easy to use and obviously visual.
Take the necessary time to build an interface, simple, efficient, easy to use as needed.
Your VBA automation often requires working on a database, which can correspond to .csv, .xls, .xlsx … files stored in your machine. It is, therefore, useful to ask the user for the path of these files beforehand.
For that, a useful and design interface might look like this one.
Behind “Load” buttons macros are linked to interact with windows explorer objects. You can assign a macro to a button, image, icon…
To do it, right-click on the object and select assign the macro, then select your procedure.
In this example, the two load buttons are associated with two procedures using windows explorer to load in each cell in front of the button the path of the file and folder required.
The icon restart in the left corner is linked to a procedure to clean the macro by deleting unuseful sheets. The run button do several operations also attached to a procedure. And the button send by mail, create an email with an attached workbook in Outlook.
When you create your tool, you can also give the user the impression of navigation by hiding and unhiding sheets when he clicks on a button. This technique has proven itself, you will find it in the sheet article.
This is an example, the interface is free of your imagination, but it shows that we can draw what we want with Excel and attach the macros directly to our objects.
Get path of a workbook
To retrieve the path of your workbook with VBA you can use the path object of the workbook. In the example below, ThisWorkbook corresponds to the attached workbook of the macro.
Sub GetPathFolder()
Dim Path As String
'ThisWorkbook is the active workbook with the following code
Path = ThisWorkbook.Path
'Display the path of the workbook with a MsgBox
MsgBox Path
End Sub
Get path of the current directory
If you need to retrieve the path of your current directory, you can use the function CurDir which returns a string.
Sub GetCurrentDirectory()
Dim Path As String
Path = CurDir()
MsgBox Path
End Sub
Load path of a File
To load the path of a file you can use msoFileDialogFilePicker. It will open the windows explorer and the user can select a file in the tree view. This path can be stock in memory through a variable to be write in the worksheet if necessary as the following example.
Sub LoadPathFile()
'Work only for windows
'Open the windows explorer
With Application.FileDialog(msoFileDialogOpen)
.AllowMultiSelect = False ' To select only one file
.Show 'Display the dialog box
'Write the path of the file selected on B2
Range("B2") = .SelectedItems(1)
End With
End Sub
Load path of a Folder
To load the path of a folder, we will use an equivalent method than to load the path of a file. This time, we will use the msoFileDialogFolderPicker object. Users can pick a folder in the tree view.
It is helpful to pick a folder to save your workbook in a specific place in your machine.
Sub LoadPathFolder()
'Open the windows explorer
With Application.FileDialog(msoFileDialogFolderPicker)
.AllowMultiSelect = False ' To select only one file
.Show 'Display the dialog box
'Write the path of the folder selected on B2
Range("B2") = .SelectedItems(1)
End With
End Sub
Filters in the FileDialog properties
Using the Filters property of the FileDialog object allow you to controls the types of files that are displayed in the dialogue box.
Sub FiltersFileDialogOpen()
Dim sFileName As String
With Application.FileDialog(msoFileDialogType.msoFileDialogOpen)
'allow only one file to be selected
.AllowMultiSelect = False 'To select only one file
With Office.FileDialogFilters.Filters
.Clear 'clear filters
'add a filter, all filters must start with an asterisk
.Add "Weather", "*.xls"
.Add "Maps", "*.png,*.jpg", 1
'.Add "All Files", "*.*"
End With
End With
End Sub
Loop over files in a folder
Sometimes you need to open and retrieve some data in each excel files save in one folder.
To do it, your need is to loop through all files in your define folder. You can use the VBA Dir object which returns the name of a file, directory, or a folder. It will return the name of a file in the folder of the specific PathMainWb previously loaded.
You can use a loop that operates while the name of the file is different than blank. All instructions are written in the loop: open the file, retrieve data, concatenate files in a single file.
Sub LoopOnFilesInFolder()
WbMainName = ActiveWorkbook.Name
'Write the path of the folder in A1
'Using the Load Path Folder Sub previously
PathMainWb = Range("A1").Value2
File = Dir(PathMainWb & "\" & ".*xl??")
'Loop on all files in the folder
Do While File <> ""
'... your instructions ...
Loop
End Sub
Loop to list all files in a folder
How can you list the name of all files in a folder which contains more than two hundred files? To do this quickly with VBA without using the Dir function, we will use the FoundFiles object attached to the Application.FileSearch.
Sub ListFilesInFolder()
Dim NbFiles, k As Integer
Dim Path As String
Path = "/Users"
'All the files in the current directory
With Application.FileSearch
.LookIn = Path
.NewSearch
.FileName = "*.*"
.SearchSubFolders = False
.Execute 'Perform the search
NbFiles = .FoundFiles.Count 'Number of files in the folder
For k = 1 To NbFiles
Sheets(1).Cells(k, 2) = .FoundFiles(k)
Next k
End With
End Sub
Loop over Subfolders in a folder
Using a For Each loop it is possible to iterate over subfolders of an existing folder.
Sub DiplayFolders()
FolderPath = "C:\BITCOIN"
Set Fs = CreateObject("Scripting.FileSystemObject")
Set Fdr = Fs(FolderPath)
Set SubFolder = Fdr.SubFolders
For Each f in SubFolder
StrF = StrF & f.name 'Concatenate name folder
Next f
MsgBox StrF
End Sub
Return the most recent file of your folder
As previously to retrieve quickly the most recent file in a folder of your system we will use the Application.FileSearch. This allows you to search for files using criterias (file size, file type, file location, date of last modification).
Sub SearchRecentFile()
Dim NameFile As String
With Application.FileSearch
.LookIn = "C:\NEWS"
.FileName = ""
'Sort file by modified date and pick the first file
If .Execute(msoSortByLastModified, msoSortOrderDescending, True) > 0 Then
NameFile = .FoundFiles(1)
End If
End With
End Sub
Create a folder with today’s date using MkDir
Using a VBA code you can create a new folder in your machine thanks to the MkDir function which required as argument a string containing the pathname.
To have the today’s date we need to write it in the name of the new file with the function Now.
By the Format option, we can determine the date formatting as we want. This procedure works also for Mac OS users.
Sub CreateFolderWithTodaysDate()
Dim FolderName, FolderPath, Folder As String
Folder = "/Users/Documents/THESAURUS"
'Folder Name
FolderName = Format(Now, "dd MMM yyyy")
'Folder Path
FolderPath = Folder & FolderName
'Create Folder
MkDir FolderPath
MsgBox "Folder has created with today's date"
End Sub
Size and creation date of a Folder
To know the size and the creation date of a folder you can use the different properties as following on a folder.
The Folder object have a lot of different properties as DateCreated, size, DateLastModified, DateLastAccessed, Drive, Name, ParentFolder, Path, ShortName, ShortPath, Type…
Sub ShowFolderSize()
Set Fs = CreateObject("Scripting.FileSystemObject")
Set f = Fs.GetFolder("C:\NEWS")
s = UCase(f.Name) & " uses " & f.size & " bytes. Created the : " & f.DateCreated
MsgBox s, 0, "Folder Info"
End Sub
Delete a file
To delete a file in your computer with a VBA procedure, you can use the Kill method which required the pathname of the specific file to remove. This procedure works also for Mac OS users.
Other methods are available on the folder object such as AddFolders to create a new folder, Copy to copy a folder in another path, CreateTextFile to create a text file in a specific folder or Move to change the location of a folder.
Sub DeleteFile()
'Delete a file in your system
Dim FileToKill As String
FileToKill = "c:\Game of Thrones.txt"
If Len(Dir(FileToKill)) > 0 Then Kill FileToKill
End Sub
Delete all files in a folder
To erase all files in your machine with a VBA method without using a loop through a folder, you can use the Kill method stipulating only the path of the folder with “*.*“.
Sub DeleteAllFiles ()
Dim DeleteFile As String
DeleteFile = "C:\TestDelete\*.*"
If Len(Dir$(aFile)) > 0 Then
Kill DeleteFile
End If
End Sub
SKILLS 