WallStreetMojo

WallStreetMojo

WallStreetMojo

MENUMENU
  • Blog
  • Free Video Tutorials
  • Courses
  • All in One Bundle
  • Login
Home » Excel, VBA & Power BI » Learn VBA » VBA FileSystemObject (FSO)

VBA FileSystemObject (FSO)

By Jeevan A YJeevan A Y | Reviewed By Dheeraj VaidyaDheeraj Vaidya, CFA, FRM

Excel VBA FileSystemObject (FSO)

VBA FileSystemObject (FSO) works similar to FileDialog, used to get access to other files of the computer we are working on. We can also edit these files means read or write the file. Using FSO 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 a new folder or files, rename the existing folder or files, get the list of all the files in the folder, and also subfolder names. Finally, we can copy files from one location to another.

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

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

  1. 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.
  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 method to work with. Based on our requirements, we can choose the method of each object.

VBA FileSystemObject

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. To encourage, follow the below steps.

Step #1: Go to Tools>References.

Activate File System object step 1

Step #2 – Select ‘Microsoft Scripting Runtime’ option

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

Activate File System object step 2

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

Create an Instance of FileSystemObject

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

Popular Course in this category
Sale
VBA Training (3 Courses, 12+ Projects)
4.6 (247 ratings)
3 Courses | 12 Hands-on Projects | 43+ Hours | Full Lifetime Access | Certificate of Completion
View Course

To create the instance, first declare the variable as FileSystemObject.

Instance File system Object 1

As we can see, FileSystemObject appears 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.

Instance File system Object 1-1

Now we can access all the options of FSO(FileSystemObject).

Instance File system Object 1-2

Examples to use VBA FileSystemObject

You can download this VBA FileSystemObject Excel Template here – VBA FileSystemObject Excel Template

Example #1 – Find the Total Drive Space

The below code will give the 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

VBA FileSystemObject Example 1

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 the Get Drive 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

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 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.”

VBA FileSystemObject Example 1-1

So, in my 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 or not, use the below code.

If the mentioned folder is available, 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

VBA FileSystemObject Example 2

Run this code through the excel Shortcut key F5 or manually, then see the result.

VBA FileSystemObject Example 2-1

Example #3 – Check Whether the File Exists or Not

The 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

VBA FileSystemObject Example 3

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

VBA FileSystemObject Example 3-1

Recommended Articles

This 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 and practical examples and a downloadable excel template. Below you can find some useful excel VBA articles –

  • ME Keyword in VBA
  • StrComp Function in VBA
  • StrConv Function in VBA
  • VBA IIF
0 Shares
Share
Tweet
Share
VBA Training (3 Courses, 12+ Projects)
  • 3 Courses
  • 12 Hands-on Projects
  • 43+ Hours
  • Full Lifetime Access
  • Certificate of Completion
LEARN MORE >>
Primary Sidebar
Footer
COMPANY
About
Reviews
Contact
Privacy
Terms of Service
RESOURCES
Blog
Free Courses
Free Tutorials
Investment Banking Tutorials
Financial Modeling Tutorials
Excel Tutorials
Accounting Tutorials
Financial Statement Analysis
COURSES
All Courses
Financial Analyst All in One Course
Investment Banking Course
Financial Modeling Course
Private Equity Course
Venture Capital Course
Excel All in One Course

Copyright © 2021. CFA Institute Does Not Endorse, Promote, Or Warrant The Accuracy Or Quality Of WallStreetMojo. CFA® And Chartered Financial Analyst® Are Registered Trademarks Owned By CFA Institute.
Return to top

WallStreetMojo

Download Coursera IPO Financial Model

By continuing above step, you agree to our Terms of Use and Privacy Policy.
WallStreetMojo

Free Excel Course

Excel functions, Formula, Charts, Formatting creating excel dashboard & others

* Please provide your correct email id. Login details for this Free course will be emailed to you

Book Your One Instructor : One Learner Free Class
Let’s Get Started
Please select the batch
Saturday - Sunday 9 am IST to 5 pm IST
Saturday - Sunday 9 am IST to 5 pm IST

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

WallStreetMojo

Free Excel Course

Excel functions, Formula, Charts, Formatting creating excel dashboard & others

* Please provide your correct email id. Login details for this Free course will be emailed to you

Login

Forgot Password?

WallStreetMojo

Download VBA FileSystemObject Excel Template

Coursera IPO Financial Model & Valuation Free Download