File System Object in VBA

Iqra Hasnain May 17, 2022

VBA VBA Object

File System Object in VBA
FileSystemObject Creation in VBA

We will introduce File System Object in VBA with examples.

File System Object in VBA

The FileSystemObject (FSO) provides a variety of operations for accessing one’s laptop’s file system. This object allows us to quickly access files, directories, and drives and read and write to them.

The FSO operates according to worldwide standards and system settings. If we’re sharing the Excel application internationally, the FSO will handle any variations in settings amongst countries that one’s code might struggle to handle.

The FSO will allow us to accomplish practically everything it can do in Windows File Explorer in VBA code. Indeed, it offers full control over the Windows file system.

`FileSystemObject` Creation in VBA

Excel VBA does not include the FileSystemObject. Perhaps, we could use FSO in VBA by constructing a late-binding object.

Code:

# vba
Sub CreateFileSysObj()

Set MyFileSysObj = CreateFileSysObj("Scripting.FileSystemObject")

End Sub

We can also include a reference to the FSO library in VBA. Early binding is speedier than late binding because the object does not need to be generated when the code is executed.

To add a link, open the Visual Basic Editor (VBE) by pressing Alt-F11, then select 'Tools|References' from the VBE menu. This can bring up a pop-up window where we may choose the appropriate reference.

Scroll to the bottom until we find 'the Microsoft Scripting Runtime' inside the column of accessible references. Select the box and click OK to include the library in the application.

The DLL library file is located on C:Windows\SysWOW64\scrrun.dll.

If we’re sending the program to other coworkers or places, make sure they have this file in the right place on their pc, or the code will fail. It’s recommended to use the Dir command to check that the file exists in an error trap on the WorkbookOpen event.

If it’s missing, display a message and exit the Excel file. After we’ve inserted the reference, we may generate the FSO with the following code.

Code:

# vba
Sub TestFileSysObj()

Dim MyFileSysObj As New FileSystemObject

End Sub

The following code will determine whether or not the folder named 'Test' exists (in the particular location). If the folder is there, the IF condition is True, and the message box displays 'The Folder Exists.' If it doesn’t exist, the text 'The Folder Does Not Exist' appears.

Code:

# vba
Sub FolderExistCheck()
Set MyFileSysObj = CreateObject("Scripting.FileSystemObject")

If MyFileSysObj.FolderExists("D:\Test") Then
    MsgBox"This Folder Exists"
Else
    MsgBox"This Folder Doest Not Exists"
End If
End Sub

Output:

Using file system object to check folder exists or not in VBA

Similarly, we can determine whether or not a file exists. The code below determines whether or not a file named Test.xlsx exists in the provided folder.

Code:

# VBA
Sub CheckFileExist()
Set MyFileSysObj = CreateObject("Scripting.FileSystemObject")

If MyFileSysObj.FileExists("D:\Test\Test.xlsx") Then
    MsgBox "This File Exists In Folder"
Else
    MsgBox "This File Does Not Exist In Folder"
End If
End Sub

Output:

Using file system object to check file inside a folder in VBA

The code below will create a folder called 'Test' in our system’s C disc (we will have to provide the path on our computer where we would like to create the folder).

Code:

# vba
Sub CreateNewFolder()
Set MyFileSysObj = CreateObject("Scripting.FileSystemObject")
MyFileSysObj.CreateFolder("D:\Test")
End Sub

Output:

Using file system object to create a new folder in VBA

While this code functions well, it will throw an exception if the folder already exists. The code below checks whether the folder already exists and, if not, creates one.

It displays a notification if the folder already exists. We utilized the FSO’s Folder Exists method to determine whether the folder exists.

Code:

# vba
Sub CreateNewFolder()
Set MyFileSysObj = CreateObject("Scripting.FileSystemObject")
If MyFileSysObj.CreateFolder("D:\Test") Then
    MsgBox "This Folder Exists Already"
Else
    MyFileSysObj.CreateNewFolder("D:\Test")
End If
End Sub

Output:

Using file system object to create a new folder in VBA

The following code copies all the files from the root folder to the Specified folder.

Code:

# vba
Sub FetchFileName()
Set MyFileSysObj = CreateObject("Scripting.FileSystemObject")
Dim FileInFolder
Dim SysFolder

Set SysFolder = MyFileSysObj.GetFolder("D:\Test")

For Each FileInFolder In SysFolder.Files
    Debug.Print FileInFolder.Name
Next FileInFolder

End Sub

Output:

checking file names from folder using file system object in VBA

This example is more complex than the ones we have already discussed before. When we refer to the Microsoft Scripting Runtime Library, we can use FileSystemObject and all other file and folder objects.

As mentioned in the above example, we have used three objects – FileSystemObject, File, and Folder. This allows us to go through each file in the required folder, and we use the name property to get the list of all file names.

Note that we are using Debug.Print to get the names of all the files. Let’s go to another example in which we will give the names of all the sub-folders in the required folder.

The idea will be the same as discussed in the above examples. Instead of files, in these examples, we will use the sub-folders.

Code:

# vba
Sub FetchSubFolder()
Set MyFileSysObj = CreateObject("Scripting.FileSystemObject")
Dim FileInFolder
Dim SysFolder
Dim SysSubFolder

Set SysFolder = MyFileSysObj.GetFolder("D:\Test")

For Each SysSubFolder In SysFolder.SubFolders
    Debug.Print SysSubFolder.Name
Next SysSubFolder

End Sub

Output:

checking sub folder names from folder using file system object in VBA

Let’s discuss another example in which we will overwrite files, as shown below.

Code:

# vba
Sub CopyFiles()
Set MyFileSysObj = CreateObject("Scripting.FileSystemObject")
Dim SysFile
Dim SrcFolder
Dim FinalFolder
Dim SysFolder
Dim SysSubFolder

SrcFolder = "D:\Test\Src"
FinalFolder = "D:\Test\Dst"

Set SysFolder = MyFileSysObj.GetFolder(SrcFolder)

For Each SysFile In SysFolder.Files
    MyFileSysObj.CopyFile Source:=MyFileSysObj.GetFile(SysFile), _
    Destination:=FinalFolder & "\" & SysFile.Name, Overwritefiles:=False
Next SysFile

End Sub

Output:

Src Folder:

src folder for transferring files from src to dst using file system object in VBA

Dst Folder:

dst folder after transferring files from src to dst using file system object in VBA

Note that we set the Overwritefiles property to False in the MyFileSysObj.CopyFile function (this is true by default). This ensures that the file is not duplicated if it already exists in the folder (and we will observe an error).

Perhaps, if we set this to True or delete Overwritefiles, any files in the final folder with the exact name will be rewritten. When transferring files, there’s always the risk of overwriting them.

In this scenario, it’s a good idea to provide the timestamp alongside the name. This ensures that the file names are always unique and that we can trace which files were copied and when.

If we only want to replicate files with a specific extension, use an IF and Then statement to test whether the extension is of a Microsoft spreadsheet excel file or not.

Code:

# vba
Sub CopyXlFiles()
Set MyFileSysObj = CreateObject("Scripting.FileSystemObject")
Dim SysFile
Dim SrcFolder
Dim FinalFolder
Dim SysFolder
Dim SysSubFolder

SrcFolder = "D:\Src"
FinalFolder = "D:\Dst"

Set SysFolder = MyFileSysObj.GetFolder(SRCFolder)

For Each SysFile In SysFolder.Files
    If MyFileSysObj.GetExtensionName(SysFile) = "xlsx" Then
        MyFileSysObj.CopyFile Source:=MyFileSysObj.GetFile(SysFile), _
    Destination:=FinalFolder & "\" & SysFile.Name, Overwritefiles:=False
    End If
Next SysFile

End Sub

File System Object in VBA

FileSystemObject Creation in VBA

`FileSystemObject` Creation in VBA