Excel VBA FileSystemObject (FSO)

We can do many tasks by using FSO, like checking whether the folder is available, creating a new folder or files, renaming the existing folder or files, getting the list of all the files in the folder, and subfolder names. Finally, we can copy files from one location to another.

Even though other functions are available to work with folders and files, FSO is the easiest method to work with folders and files by keeping the VBA codeVBA CodeVBA code refers to a set of instructions written by the user in the Visual Basic Applications programming language on a Visual Basic Editor (VBE) to perform a specific task.read more neat and straight.

We can access four types of objects with FileSystemObject. Below are those.

  • Drive: Using this object, we can check whether the mentioned drive exists or not. We can get the pathname, type of purpose, and size of the enterprise.Folder: This object allows us to check whether the particular folder exists or not. We can create, delete, modify, and copy folders using this object.File: This object allows us to check whether the particular file exists or not. We can create, delete, modify, and copy files using this VBA object.Text Stream: This object allows us to create or read text files.

All the above methods have their method to work with. Based on our requirements, we can choose the method for each object.

You are free to use this image on you website, templates, etc., Please provide us with an attribution linkHow to Provide Attribution?Article Link to be HyperlinkedFor eg:Source: VBA FileSystemObject (FSO) (wallstreetmojo.com)

How to Enable FileSystemObject?

It is not readily accessible in VBA. Since accessing files and folders is the outside task of Excel, we need to enable the FileSystemObject.

Examples to use VBA FileSystemObject

Example #1 – Find the Total Drive Space

The below code will give the total space of the drive.

  • Go to Tools > References. Select the “Microsoft Scripting Runtime” option. Next, scroll down and select the “Microsoft Scripting Runtime” option. After choosing the options, click on “OK.” Now, we can access the FileSystemObject (FSO) in VBA. Create an Instance of FileSystemObject Once we enable the “Microsoft Scripting Runtime” option from the Objects library, we need to create a FileSystemObject (FSO) through coding. To create the instance, first declare the variable as FileSystemObject. As we can see, FileSystemObject appears in the IntelliSense list in VBA. Therefore, it wouldn’t have been available before we enabled the ‘Microsoft Scripting Runtime.’ Since FSO is an object, we need to set it to create a new instance. Now, we can access all the options of FSO(FileSystemObject).

Now, we can access the FileSystemObject (FSO) in VBA.

Once we enable the “Microsoft Scripting Runtime” option from the Objects library, we need to create a FileSystemObject (FSO) through coding.

Code:

Sub FSO_Example1()

Dim MyFirstFSO As FileSystemObject
Set MyFirstFSO = New FileSystemObject

Dim DriveName As Drive
Dim DriveSpace As Double

Set DriveName = MyFirstFSO.GetDrive("C:")
'Create new drive object

DriveSpace = DriveName.FreeSpace
'This will get the free space of the drive "C"

DriveSpace = DriveSpace / 1073741824
'This will convert the free space to GB

DriveSpace = Round(DriveSpace, 2)
'Round the total space

MsgBox "Drive " & DriveName & " has " & DriveSpace & "GB"

End Sub

Break down the code.

First, we created an instance of FSO.

Dim MyFirstFSO As FileSystemObject Set MyFirstFSO = New FileSystemObject

Next, we have declared two variables.

Dim DriveName As Drive Dim DriveSpace As Double

Since DriveName is an Object variable, we need to set this to FSO, one of the FSO methods. Since we need the drive’s characteristics, we have used the “GetDrive” option and mentioned the drive name.

Set DriveName = MyFirstFSO.GetDrive(“C:”)

For another variable, DriveSpace, we will assign the free space method of the drive we are accessing.

DriveSpace = DriveName.FreeSpace

Currently, the above equation can get us free space of the drive “C.” So to show the result in GB, we have divided the open space by 1073741824.

DriveSpace = DriveSpace / 1073741824

Next, we will round the number.

DriveSpace = Round(DriveSpace, 2)

Finally, show the result in Message Box.

MsgBox “Drive " & DriveName & " has " & DriveSpace & “GB”

When we run the code manually or through shortcut key F5, then in the message box, we will get the free space of the drive “C.”

So, our computer Drive C has 216.19 GB of free space memory.

Example #2 – Check Whether the Folder Exists or Not

To check whether the particular folder exists, use the code below.

If the mentioned folder is available, it will show the message box as “The Mentioned Folder is Available.” If not, it will show the VBA message boxVBA Message BoxVBA MsgBox function is an output function which displays the generalized message provided by the developer. This statement has no arguments and the personalized messages in this function are written under the double quotes while for the values the variable reference is provided.read more as “The Mentioned Folder is Not Available.”

Sub FSO_Example2()

Dim MyFirstFSO As FileSystemObject
Set MyFirstFSO = New FileSystemObject

If MyFirstFSO.FolderExists("D:Excel FilesVBAVBA Files") Then
MsgBox "The Mentioned Folder is Available"
Else
MsgBox "The Mentioned Folder is Not Available"
End If

End Sub

Run this code through the excel Shortcut keyExcel Shortcut KeyAn Excel shortcut is a technique of performing a manual task in a quicker way.read more F5 or manually, then see the result.

Example #3 – Check Whether the File Exists or Not

The below code will check whether the mentioned file is available or not.

Sub FSO_Example3()

Dim MyFirstFSO As FileSystemObject Set MyFirstFSO = New FileSystemObject

If MyFirstFSO.FileExists(“D:Excel FilesVBAVBA FilesTesting File.xlsm”) Then MsgBox “The Mentioned File is Available” Else MsgBox “The Mentioned File is Not Available” End If

End Sub

Run this code manually or use the F5 key, then see the result.

This article has been a guide to VBA FileSystemObject. Here, we learn how to use FileSystemObject (FSO) in VBA to access files and folders from the computer, practical examples, and a downloadable Excel template. Below you can find some useful Excel VBA articles: –

  • ME Keyword in VBAStrComp Function in VBAStrConv Function in VBAVBA IIF