VBA FileSystemObject (FSO)

Introduction to FileSystemObject

Why do we use VBA? We use VBA to automate our tasks. Normally in excel we only work with data present in rows and columns which are in the form of tables or something. But what about the files and folders which are not a part of excel? What if we need to use that data from any file how do we access it. This is where FileSystemObject comes in handy.

FileSystemObject or FSO is used to access the system of files from our computer. Using this we can have access to all the files folders we have present on the computer we are working on. FSO is basically an API tool which is used to access other files. Now, this is not present by default in VBA which we will learn later on in this article. Let us first understand what FSO is. Earlier in VBA, we had DIR function which was referred to access other files and folders from a computer. The coding for DIR function was very complex to work with. But in FSO things are different.

Now there are four types of objects which we can access by FSO and they are as follows:

• Drive: Which is used to have access to a mentioned drive.
• Folder: This is used to have access to a mentioned folder.
• File: This is used to have access to a mentioned file.
• Text Stream: With this object, we can read or write a text file.

Each of the object listed above has different methods to use them. For example, if we want to copy a file we will use the CopyFile method or delete a folder we will use the DeleteFolder method and so on.

As I have discussed above FSO is not present by default in VBA, there are two methods to enable FSO in VBA.

• The first method is through setting the reference.
• The second method is to refer to the library from the code.

While the second method is more complex it is always recommended to use the first method which is very simple. Let us follow these basic steps.

In VBA, Go to Tools Tab and then go to References,

A wizard box will pop up, select Microsoft Scripting Runtime as shown below and press OK.

Now we can access FSO in VBA. Now let us use this through some examples and learn about it.

How to Use VBA FileSystemObject in Excel?

Below are the different examples to use VBA FileSystemObject Function in Excel

VBA FileSystemObject – Example #1

Before we begin to use FSO in Vba first let us learn how to create instances in VBA.

Step 1: In the sub module create a sub procedure,

Code:

Sub Newfso()

End Sub

Step 2: Declare a variable as FileSystemObject as shown below,

Code:

Sub Newfso()

Dim A As FileSystemObject

End Sub

Step 3: Now we need to create an instance as FSO is an object using the SET statement as shown below,

Code:

Sub Newfso()
Dim A As FileSystemObject
Set A = New FileSystemObject
End Sub

Now, this statement will allow us to create or modify files or folders using FSO.

Step 4: Now we can see the IntelliSense feature after enabling FSO. Use dot operator as follows,

Code:

Sub Newfso()

Dim A As FileSystemObject
Set A = New FileSystemObject
A.

End Sub

It gave us various options using the IntelliSense feature. This is how we create instances using FSO.

VBA FileSystemObject – Example #2

Now since we have created an instance in Example 1 let us move further to use it and check whether a file or folder exists or not.

Step 1: After we have created a new FileSystemObject, use IF statement to determine whether a folder exists or not as follows,

Code:

Sub Newfso()
Dim A As FileSystemObject
Set A = New FileSystemObject
If A.FolderExists("C:\Users\Public\Project") Then

End Sub

Step 2: If the folder exists we want to display folder exists and if does not we want it to display that folder does not exists.

Code:

Sub Newfso()
Dim A As FileSystemObject
Set A = New FileSystemObject
If A.FolderExists("C:\Users\Public\Project") Then
MsgBox "The Folder Exists"
Else
    MsgBox "The Folder Does Not Exist"
End If

End Sub

Step 3: Now execute the above code and see the result as follows,

The folder does exist in my desktop so we get the message that folder exists.

VBA FileSystemObject – Example #3

Now since we discussed that FSO has various objects such as drives. Let us find out how much space I have available in my E drive.

Step 1: Start by another subprocedure as follows,

Code:

Sub Newfso1()

End Sub

Step 2: Now declare a variable as FileSystemObject and set it to new instance as follows,

Code:

Sub Newfso1()

Dim A As FileSystemObject
Set A = New FileSystemObject

End Sub

Step 3: Now since we are using the Drive property declare one variable as Drive type and one variable as double to hold the data for space as follows,

Code:

Sub Newfso1()

Dim A As FileSystemObject
Set A = New FileSystemObject
Dim D As Drive, Dspace As Double

End Sub

Step 4: Now let us create a new drive object as shown below,

Code:

Sub Newfso1()
Dim A As FileSystemObject
Set A = New FileSystemObject
Dim D As Drive, Dspace As Double
Set D = A.GetDrive("C:")

End Sub

This is one of the FSO methods we are using to access the drive.

Step 5: We will use another FSO method to get the free space of the drive and store it in the variable defined for storing the drive space,

Code:

Sub Newfso1()
Dim A As FileSystemObject
Set A = New FileSystemObject
Dim D As Drive, Dspace As Double
Set D = A.GetDrive("C:")
Dspace = D.FreeSpace

End Sub

Step 6: Now let us calculate the space in GB as follows,

Code:

Sub Newfso1()
Dim A As FileSystemObject
Set A = New FileSystemObject
Dim D As Drive, Dspace As Double
Set D = A.GetDrive("C:")
Dspace = D.FreeSpace
Dspace = Round((Dspace / 1073741824), 2)

End Sub

Step 7: Now display the value stored in for Drive space using the msgbox function as follows,

Code:

Sub Newfso1()
Dim A As FileSystemObject
Set A = New FileSystemObject
Dim D As Drive, Dspace As Double
Set D = A.GetDrive("C:")
Dspace = D.FreeSpace
Dspace = Round((Dspace / 1073741824), 2)
MsgBox "The Drive " & D & " has " & Dspace & "GB free Space"

End Sub

Step 8: Execute the above code to find out the below result,

VBA FileSystemObject – Example #4

Now let us create a new folder to a specified location using FSO.

Step 1: Again let us start with another subprocedure as follows,

Code:

Sub Newfso2()

End Sub

Step 2: Let us follow the same steps and create an instance as follows,

Code:

Sub Newfso2()

Dim A As FileSystemObject
Set A = New FileSystemObject

End Sub

Step 3: Now we will use Create Folder method to create a new folder to a specified location,

Code:

Sub Newfso2()

Dim A As FileSystemObject
Set A = New FileSystemObject
A.CreateFolder ("C:\Users\Public\Project\FSOExample")

End Sub

Step 4: Execute the above code and see the result in the desktop as follows,

We have successfully created a new folder to the specified location.

Things to Remember in VBA FileSystemObject

• FSO is an API application Tool.
• FSO is not by default available in VBA.
• With FSO we can create, modify or read files and folders in a computer.
• We can also use FSO for our network drives.

Recommended Articles

This is a guide to VBA FileSystemObject. Here we discuss how to use VBA FileSystemObject in Excel along with some practical examples and a downloadable excel template. You can also go through our other suggested articles –

1. VBA Workbook
2. VBA RGB
3. VBA Count
4. VBA XML