VBA FileSystemObject (FSO)

Excel VBA File System Object

FileSystemObject in VBA Excel allows us to get or access the files from the computer, PC, or Laptop we are working on at the moment. Using VBA FSO (FileSystemObject) we can access files, work with them, modify files and folders. FSO is the important API tool we can access with VBA. As part of the VBA project, we may need to access a few folders and files in our computer to get the job done.

We can do many tasks by using FSO like “to check whether the folder is available or not”, Create new folder or files, rename the existing folder or files, get the list of all the files in the folder and also subfolder names, and finally we can copy files from one location to another.

Even there are other functions available to work with folders and files, FSO (FileSystemObject) is the easiest method to work with folders and files by keeping the VBA code neat and straight.

We can access 4 types of Objects with File System Object in VBA. Below are those.

1. Drive: Using this object we can check whether the mentioned drive exists or not, we can get the path name, type of drive, and size of the drive.
2. Folder: This object allows us to check whether the particular folder exists or not. We can create, delete, modify, copy folders using this object.
3. File: This object allows us to check whether the particular file exists or not. We can create, delete, modify, copy files using this vba object.
4. Text Stream: This object allows us to create or read text files.

All the above methods have their own method to work with. Based on our requirement we can choose the method of each object.

How to Enable FileSystemObject in Excel VBA?

FileSystemObject (FSO) by default is not readily accessible in Excel VBA. Since accessing files and folders is the outside task of excel we need to enable the FSO (FileSystemObject). To enable follow below steps.

Step #1

Go to Tools > References.

Step #2

Scroll down and select ‘Microsoft Scripting Runtime’ option. After selecting the options click on OK.

Now we can access the File System Object (FSO).

Create an Instance of FileSystemObject through Excel VBA Code

Once the ‘Microsoft Scripting Runtime’ option is enabled from Objects library, we need to create the instance of File System Object (FSO) through coding.

To create the instance first declare the variable as FileSystemObject.

As we can see FileSystemObject is appearing in the IntelliSense list in VBA. This wouldn’t have available before we enable 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).

Examples of Using FileSystemObject in Excel VBA

Below are the examples of using the File System Object (FSO) in VBA.

Example #1 – Find the Total of the Drive

Below code will give total space of the drive.

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 of 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 method. Since we need the characteristic of the drive we have used Get Drive option and mentioned the drive name

Set DriveName = MyFirstFSO.GetDrive("C:")

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

DriveSpace = DriveName.FreeSpace

As of now, the above equation can get us free space of the drive “C”. So to show the result in GB we have divided the free 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 message box we will get the free space of the drive “C”.

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

Example #2 – Check Whether the FolderEexists or Not

To check whether the particular folder exists or not use the below code.

If the mentioned folder is available then it will show us the message box as “The Mentioned Folder is Available”, if not it will show the VBA message box as “The Mentioned Folder is Not Available”.

Code:

Sub FSO_Example2()

    Dim MyFirstFSO As FileSystemObject
    Set MyFirstFSO = New FileSystemObject

    If MyFirstFSO.FolderExists("D:\Excel Files\VBA\VBA 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 key F5 or manually, then see the result.

Example #3 – Check Whether the File Exists or  Not

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

Code:

Sub FSO_Example3()

   Dim MyFirstFSO As FileSystemObject
   Set MyFirstFSO = New FileSystemObject

   If MyFirstFSO.FileExists("D:\Excel Files\VBA\VBA Files\Testing 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 using the F5 key, then see the result.

Recommended Articles

This has been a guide to VBA FileSystemObject. Here we learn how to use File System Object (FSO) in VBA excel to access files and folders from the computer along with practical examples and a downloadable template. Below you can find some useful excel VBA articles –

• Examples to Rename Worksheets using VBA Code
• Function of TimeValue in VBA
• DatePart Function in VBA
• StrComp Function in VBA
• StrConv Function in VBA
• IIF Function in VBA
• Call Sub in Excel VBA