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