Project Explorer

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. 

Alpes, France – Photo taken by me

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.

Starter Menu for VBA Macro

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

Assign a VBA macro

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 ...

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
        .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 & '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.

create folder with today's date mac os
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


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s