VBA GetFolder & GetFile (Get File & Folder Properties)

This tutorial will demonstrate how to use the GetFolder and GetFile methods of the FileSystemObject.

Get Folder and File Properties with VBA FileSystemObject

The GetFolder method returns a Folder object corresponding to the folder in a specified path and lets you access its properties. The GetFile method does the same with the file specified.

Set VBA Reference

First, when using FileSystemObjects, you may have to set a reference to the VB script run-time library: open the Visual Basic Editor (ALT+F11), select Tools > References from the drop-down menu and tick the check-box of ‘Microsoft Scripting Runtime’.

FileSystemObject

Second, you must create the FileSystemObject:

Dim FSO As New FileSystemObject
Set FSO = CreateObject("Scripting.FileSystemObject")

Now you have access to GetFolder, and the other FileSystemObject Methods.

Use of GetFolder Method

After specifying the folder you want to access

Set fld = FSO.GetFolder("C:\Src\")

you can copy the folder:

fld.Copy "C:\NewFolder\"

move the folder:

fld.Move "C:\NewFolder\"

delete the folder:

fld.Delete

or create a new text file in the folder:

fld.CreateTextFile “NewTextFile.txt"

By using this method, you get access to the folder’s properties such as its attributes (fld.Attributes), the date and time when it was created (fld.DateCreated), last accessed (fld.DateLastAccessed), last modified (fld.DateLastModified), its drive’s letter (fld.Drive), its name and short name (fld.Name, fld.ShortName), its path and short path (fld.Path, fld.ShortPath), its size (fld.Size), its type (fld.Type), its parent folder (fld.ParentFolder), check whether it is a root folder (fld.IsRootFolder) or you can loop through, count, etc. its files (fld.Files) or subfolders (fld.SubFolders).

Putting this all together in a procedure would look like this:

Sub FSOGetFolder()
    Dim FSO As New FileSystemObject
    Set FSO = CreateObject("Scripting.FileSystemObject")
    Set fld = FSO.GetFolder("C:\Src\")

    Debug.Print fld.DateCreated
    Debug.Print fld.Drive
    Debug.Print fld.Name
    Debug.Print fld.ParentFolder
    Debug.Print fld.Path
    Debug.Print fld.ShortPath
    Debug.Print fld.Size
    Debug.Print fld.Files.Count
    Debug.Print fld.Type

    For Each fold In fld.SubFolders
        Debug.Print fold.Name
    Next fold

    For Each fil In fld.Files
        Debug.Print fil.Name
    Next fil

End Sub

Please note that you can press Ctrl + G to see the result of Debug.Print in the VBA immediate window.

GetParentFolderName Method

Alternatively to the above mentioned way, you can access a folder’s parent folder’s name by using this code:

Dim FSO As New FileSystemObject
Set FSO = CreateObject("Scripting.FileSystemObject") 

ParentFold= FSO.GetParentFolderName("C:\ParentTest\Test\")

ParentFold will be in this case “C:\ParentTest\”.

Please note that this method will not resolve the path, nor does it check for the existence of the specified path.

GetSpecialFolder Method

With the GetSpecialFolder method, by passing 0, 1 or 2 as an argument, you can get your Windows folder path (with files installed by the Windows operating system), your system folder path (with libraries, fonts, and device drivers) and temporary folder path (the folder that is used for storing temporary files), respectively.

Sub FSOGetSpecialFolder()
    Dim FSO As New FileSystemObject
    Set FSO = CreateObject("Scripting.FileSystemObject")

    Debug.Print FSO.GetSpecialFolder(0) ‘Result can be: C:\Windows\System32

End Sub

GetFile Method

You can use the GetFile method in a very similar way as the GetFolder method. After specifying the file you want to access

Set fil = FSO.GetFile("C:\Src\Test.xlsx")

you can copy the file:

fil.Copy "C:\Dst\"

move the file:

fil.Move "C:\Dst\"

delete the file:

fil.Delete

or open it as a TextStream object:

fil.OpenAsTextStream

The file’s properties such as its attributes, the date and time when it was created, last accessed or last modified, its drive’s letter, name and short name, path and short path, size, type and its parent folder can be accessed the same way as described at the GetFolder method.