VBA FileSystemObject

What is Excel VBA File System Object (FSO)?

FileSystemObject (FSO) is a VBA feature that helps access our computer file system. We can read, edit, and delete files and folders using it. We can also check if a particular folder exists, copy files from one folder to another, and perform many other file-related tasks with the FileSystemObject (FSO).

We work with ranges of cells, worksheets, workbooks, and many other objects to automate tasks using Excel. However, to access external files and folders, VBA is a powerful tool.

Explanation and Uses

FileSystemObject (FSO) is essential to access objects like files and folders outside Excel. For example, we can go through all the following objects using the VBA FileSystemObject (FSO).

Drive: The drive is the first object we must access for folders, subfolders, and files. Using the FileSystemObject (FSO), we can check if a drive exists, its path, type, size, etc.

Folder: A folder is used to save files. Using the FileSystemObject (FSO), we can create folders, check if a particular folder exists, modify a folder, delete it, and copy it from one location to another.

File: Using the VBA FileSystemObject (FSO), we can create files, check if a particular file exists, and create, modify, delete, and copy from one folder to another.

Text Stream: The textStream object allows you to create or read text files.

Some of the actions we can perform with the FileSystemObject (FSO) are:

• VBA filesystemobject getfolder: This will help us to check the desired folder.
• VBA filesystemobject getfile: This will help us check whether the given file is available in the path.

For example, if we want to create a new folder on our computer, we can use the “Create Folder” method of the Folder object. The following code will create a folder.

Sub FSO_Example()
Dim MyFSO As FileSystemObject
Set MyFSO = New FileSystemObject
MyFSO.CreateFolder “C:\Users\Jeevan A Y\Desktop\FSOTest”
End Sub

This code will create a folder “FSOTest” on the desktop. Once we run the code, you can see a folder named “FSOTest” on the desktop.

Excel VBA – All in One Courses Bundle (35+ Hours of Video Tutorials)

If you want to learn Excel and VBA professionally, then ​Excel VBA All in One Courses Bundle​ (35+ hours) is the perfect solution. Whether you’re a beginner or an experienced user, this bundle covers it all – from Basic Excel to Advanced Excel, Macros, Power Query, and VBA.

How to Enable FileSystemObject (FSO)?

The FileSystemObject (FSO) is not enabled in Excel VBA. However, we can access VBA FileSystemObject by enabling it in two ways. First, since we will deal with files and folders outside the Excel object, we must set the reference to the library which holds these external objects, i.e., drives, folders, and files.

There are a couple of ways to enable the FileSystemObject (FSO) in Excel.

1. Setting a reference to the Microsoft Scripting Runtime Library.
2. Creating an object that refers to the library within the VBA code itself.

Both methods work fine. However, this article will concentrate on referencing Microsoft Scripting Runtime Library, which is easier for beginners to understand.

Set Reference to Microsoft Scripting Runtime Library

Before we set the reference, check whether the VBA FileSystemObject is already enabled on your computer.

Define a variable and try to set its data type to FileSystemObject.

When we try to set the reference of VBA FileSystemObject, we don’t see it in the IntelliSense list. Hence, it is not enabled on your computer. However, it is enabled if the FileSystemObject appears in the IntelliSense list.

Here’s how you enable the FileSystemObject step-by-step.

Step 1: Open the Visual Basic Editor window by pressing the shortcut key ALT + F11. It will open the VBE window.

Step 2: In the Visual Basic Editor window, click the “Tools” tab, and then click on “References.”

Step 3: It will open the following “References – VBA Project” window.

In the References window, scroll down and choose “Microsoft Scripting Runtime.”

Step 4: Click on “Ok. ” You can now access File System Object on your computer.

We can access VBA FileSystemObject in our Visual Basic Editor window.

Create an Instance of FileSystemObject in VBA Code

There needs to be more than just enabling the FileSystemObject to ensure the job is done; instead, we must create an instance for the VBA FileSystemObject within the code.

Let’s follow the steps below to create an instance of the FileSystemObject.

Step 1: Define a variable using the DIM statement.

Step 2: After defining the variable, assign it the FileSystemObject data type.

Step 3: Since the defined variable holds an object data type, we must use the SET keyword to assign the object to it.

Use the SET keyword along with an equal sign.

Step 4: This is the most important step while creating an instance of the FileSystemObject.

In the SET keyword, we must assign the FileSystemObject with a new instance as follows.

It will create a new instance of the FileSystemObject. Now the variable “MyFSO” holds its reference. We can access all the properties and methods of the FileSystemObject through it.

To test this, enter the variable name and a dot to see the properties and methods of the FileSystemObject.

It is one of the benefits of creating an instance of a VBA FileSystemObject, where we can see the IntelliSense list shows up, which helps make the automation task more manageable.

Examples

Now let us look at examples of using the FileSystemObject with a few examples.

Example #1 – Find the Total Drive Space

Using the FileSystemObject, we can find the size of our computer’s drive. Next, follow the steps listed below for the VBA code.

Step 1: Create an instance of the FileSystemObject by defining the variable and later assigning the FileSystemObject to it using the SET statement in VBA.

Step 2: Next, define another variable and assign its data type as “Drive”.

Step 3: The assigned data type “Drive” is an object data type; hence, we must assign the Drive object to the defined variable using the SET keyword.

While assigning the drive to the variable, we have used the FileSystemObject variable “MyFSO” and used the “GetDrive” method to assign the “C” drive to it.

Step 4: Define another variable to assign the size of the drive.

Step 5: Now, we must assign drive space for the defined variable. Since the drive variable “MyDrive” holds the reference of the FileSystemObject, we can use it to get the drive space.

MyDrive.TotalSize gives us the “C” drive total space.

Step 6: Show the total drive space in a message box.

Execute the code to see the total disk space in the “C” drive.

The result obtained is a huge number. However, this is unclear because it is in “Bytes” and should be converted to GB.

Divide the above number by 1073741824 (1 GB) to convert the space in “Bytes” into GBs.

So, the total space available in the “C” drive is 476 GB. We can check our computer’s “C” drive to confirm this.

The complete code is below for your reference.

Sub FSO_FindDisk_Space()

'Create an instance of FileSystemObject
Dim MyFSO As FileSystemObject
Set MyFSO = New FileSystemObject

'Get the drive name to the variable
Dim MyDrive As Drive
Set MyDrive = MyFSO.GetDrive("C:")

'Get the total size of the drive
Dim MyDrive_Space As Double
MyDrive_Space = MyDrive.FreeSpace

'Convert and Show the drive space in GB
MsgBox (MyDrive_Space / 1073741824)

End Sub

The above code shows us the total space of the given drive; we can also find the drive’s free space using the following code.

Sub FSO_FindDisk_Space()

'Create an instance of FileSystemObject
Dim MyFSO As FileSystemObject
Set MyFSO = New FileSystemObject

'Get the drive name to the variable
Dim MyDrive As Drive
Set MyDrive = MyFSO.GetDrive("C:")

'Get the total size of the drive
Dim MyDrive_Space As Double
MyDrive_Space = MyDrive.FreeSpace

'Convert and Show the drive space in GB
MsgBox (MyDrive_Space / 1073741824)

End Sub

The only difference between the previous and this code is the yellow highlighted area. We have used “FreeSpace” instead of the “TotalSize” here. It will show us the free space of the C drive.

So, the total free space of the C drive is 262 GB.

Example #2 – Check Whether the Folder Exists or Not

Using the FileSystemObject, we can check if any particular folder exists. It is important to check if a particular folder exists on our computer to get the file from a specific folder.

For example, look at the following folder.

We have a folder called “FSOTest” on our desktop. Let’s check if this folder exists by using the following code.

When we run this code, it will give us the following result in a message box.

Since we have the folder “FSOTest” on our desktop, we get the result “The given folder exists in the given path.”

If the given folder does not exist in the path, we will get the following result.

Example #3 – Check Whether the File Exists or Not

Once we check if the folder exists, the next step is to get inside the folder and check if the required file exists. We can check if a file exists using its name and extension.

The code looks similar to the previous example, we only make two changes:

1. Follow the folder name with the desired file name as follows:

“C:\Users\User\Desktop\FSOTest\Sales Report 2023.xlsx”

After the folder name “FSOTest,” we have entered the backslash followed by the file name, i.e., “Sales Report 2023.xlsx”.

• Instead of the FolderExists method, we must choose the “FileExists” method.

The complete code is as follows.

Sub FSO_File_Exists()
‘Create an instance of FileSystemObject
Dim MyFSO As FileSystemObject
Set MyFSO = New FileSystemObject

'Assign the file name along with the path to the variable
Dim FolderName As String
FolderName = "C:\Users\User\Desktop\FSOTest\Sales Report 2023.xlsx"

'Use the "FileExists" method to check
If MyFSO.FileExists(FolderName) Then
    MsgBox "The given file exists in the given path"
Else
    MsgBox "The given file does not exist in the given path"
End If

End Sub

Before we execute the above code, let us create the file in the folder.

Once we run the code, we will get the following result.

Since a file is available in the given path, the message box shows “The given file exists in the given path”.

If the given file is unavailable, it will display, “The given file does not exist in the given path.”

Example #4 – Get the List of all the Files in the Folder

We can loop through a folder and get all its file names. For example, look at the following folder and the files in it.

We have four files in the folder. We have one PowerPoint file and three Excel files out of these four files.

The goal is to capture and store all the files’ names in cells. For example, the following code will retrieve all the files’ names to cells.

Sub FSO_File_List()
‘Create an instance of FileSystemObject
Dim MyFSO As FileSystemObject
Set MyFSO = New FileSystemObject
‘Assign the folder name along with the path to the variable
Dim FolderName As Folder
Set FolderName = MyFSO.GetFolder(“C:\Users\User\Desktop\FSOTest”)
‘Define a variable and assign the FILE data type
Dim MyFile As File
‘Define a variable to store it in cell
Dim k As Integer
‘Start the cell address from second row
k = 2
‘Use the loops to loop through all the files
For Each MyFile In FolderName.Files
Cells(k, 1).Value = MyFile.Name
k = k + 1
Next MyFile
End Sub

It will list all the file names as follows.

Important Things to Note

• The FileSystemObject is not enabled by default in Excel.
• Since the VBA FileSystemObject refers to objects, we must create an instance of the object using the SET keyword.
• The FileSystemObject throws the “Path not found” error if the given path is unavailable in the computer.

• While referring to the file, we must provide its extension because the same file name can be given to two or more files with different file extensions.

Frequently Asked Questions (FAQs)

Download Template

This article must be helpful to understand the VBA FileSystemObject, with its formula and examples. You can download the template here to use it instantly.

Recommended Articles

Guide to VBA FileSystemObject. Here we explain how to use FSO to access our computer’s file system along with with downloadable excel template. You may learn more from the following articles –

• VBA IIF
• VBA AND Function
• VBA Case Statement