VBA File System Object - Access Folder Structure Explained

File System Object in VBA programming allows you to access your computer file and Folder structure dynamically. This object is an easy object based method, which allows you to access folder, sub-folders and point files dynamically.


To access any particular list of files or folders, you need to understand the File System Object of your computer. As shown in below figure


File System Objects comprises multiple components that you need to access programmatically. Each of objects is a collection of sub objects. In normal language you can say as FSO consists of Files, Folders and other aspect of your computer file systems.


Let us talk about each objects that constitutes File System Object


Drive - Each and every computers has a standard structure of keeping files.
           Technically Drives are the partition of your storage device created on your pc.
           This object allows you to locate or store file object or create folder object on
           specific location of your physical storage drive.


Folder - This object is a vault that helps user to keep files or folders by naming the folder
             as per the information those files hold. This object you can access
            programmatically.


File -    This object can be image, documents, applications, software or any other object
           that holds information.

Now let us start referencing File Scripting Runtime Library

To access to your folder structure through VBA, you need to reference "Microsoft Scripting Runtime " enabled in reference library, which is present in reference list in VB Editor. This is scrrun.dll file located in your C:\Windows\System32 folder. Please refer below image to know how to reference the file.

What does File System Object functions.

Broadly we can speak, this objects enables user to access folders, subfolder

Read files, delete files, point files to write

Moving, Copying or deleting files/folders.

Even you can check for any file/folder if exists or not.

Press F11 to open VB Editor
Insert Module
Ensure Reference "Microsoft Scripting Runtime" is checked in Reference Wizard
       (as shown in above image)


Let us take an example to check the available space on C: drive, write a piece of steps to get the drive specification programmatically.

'Comment1 - Creating a File System Object _
Declare variable publicly, to access the variable in your VBA project
Option Explicit
Public fso as New FileSystemObject  ' Instantiating a new public variable
'Now write a sub procedure
Sub Check_Drive_Available_Space()
'Declare variable
Dim dRName     As Drive                           'Holds Drive Name
Dim dRSpace    As Double                        'Holds available space on drive

Set dRName = fso.GetDrive("C:")                                 'Set Object

    dRSpace = dRName.FreeSpace
    dRSpace = dRSpace / 553030512640#           'Convert available Space in GB
    dRSpace = WorksheetFunction.Round(dRSpace, 2)       'Rounding Product
   
    MsgBox " C: Drive available space " & dRSpace & "GB"
End Sub

Example 2 - Check if Given folder exists or not, if not exists then create a folder


Option Explicit
'Creating a FileSystemObject
Public FSO As New FileSystemObject

Sub ChkFolder()
    Dim Fldr_name     As String         'Holder the Folder name to check


    Fldr_name = InputBox("Give me Folder Path to Check")
       
        If Len(Fldr_name) > 0 Then
            If FSO.FolderExists(Fldr_name) = True Then
                MsgBox "Folder Exists " & FSO.GetFolder(Fldr_name)
            Else
                FSO.CreateFolder (Fldr_name)
                MsgBox "Folder Created and the path is " & _
                             FSO.GetFolder(Fldr_name)
            End If
        Else
            MsgBox "Given Folder name is not in correct format"
        End If

End Sub

Example 3 - Get the list of files available in folder

'Declare Public File System Object

Public fso     As New FileSystemObject

Sub ShowListofFilesinFolder()

'Declare Variables

Dim objFldr            As Folder

Dim objFile            As File

Dim strPath           As String

Dim lngLastRow     As Long

'Provide Folder Path to get the list of files, in this case you can pass your folder location

strPath = Environ("UserProfile") & "\Your Folder Location\"

Set objFldr = fso.GetFolder(strPath)   'Set Object

'Determine if Files exists in the folder or not

If objFldr.Files.Count < 1 Then

      Msgbox "No Files Found"

      Exit Sub

End If

'Add Column Names in Sheet

Cells(1, "A").Value = "File Name"
Cells(1, "B").Value = "Size"
Cells(1, "C").Value = "Modified Date/Time"

'Determine available blank row

LastRow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row + 1

'Loop thru the folder to capture name, size and DateLastModified of each file

For Each objFile In objFldr.Files

        Cells(LastRow, 1).Value = objFile.Name
        Cells(LastRow, 2).Value = objFile.Size
        Cells(LastRow, 3).Value = objFile.DateLastModified

     LastRow = LastRow + 1
Next objFile

End Sub

With this procedure you can extract the list of available files in the folder.

Here is an another example to access File Dialog Box and select the excel files only.

write few piece of steps to execute and open Folder Wizard.



Sub Open_FileDialogBox()
'Let us learn to open File Dialog box using VBA Script
'First Ensure that you have referenced "Microsoft Runtime Scripting" _
 in Reference Library available under Tool dropdown from Ribbon

 'Declare Variable to reference
    Dim fd As FileDialog
   
 'Set variable to application
    Set fd = Application.FileDialog(msoFileDialogOpen)
   
'Set Filters to your File Dialog Box

fd.Filters.Clear
fd.Filters.Add "Show List of Excel files only", "*.xls*"
fd.FilterIndex = 1
fd.AllowMultiSelect = False
fd.Title = "Open Excel Files only"
fd.ButtonName = "You have selected 1 Excel file"
fd.Show


End Sub

You can even optimize Filters in With - End With

With fd
    .Filters.Clear
    .Filters.Add "Show List of Excel Files only ", "*.xls*"
    .FilterIndex = 1
    .Title = "Open Excel Files Only"
    .ButtonName = "You have selected 1 Excel File"
    .AllowMultiSelect = False
    .Show
End With




Hope you enjoyed the learning