EDUCBA

EDUCBA

MENUMENU
  • Blog
  • Free Courses
  • All Courses
  • All in One Bundle
  • Login
Home VBA VBA Resources VBA Object functions VBA FileSystemObject (FSO)

VBA FileSystemObject (FSO)

Madhuri Thakur
Article byMadhuri Thakur

Excel VBA FileSystemObject

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.

Watch our Demo Courses and Videos

Valuation, Hadoop, Excel, Mobile Apps, Web Development & many more.

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,

VBA Tool Box

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

Scripting RunTime

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

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

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

VBA Filesystemobject Example 1.1

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

Code:

Sub Newfso()

Dim A As FileSystemObject

End Sub

VBA Filesystemobject Example 1.2

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

VBA Filesystemobject Example 1.3

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

VBA Filesystemobject Example 1.4

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

VBA Filesystemobject Example 2.1

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

Example 2.2

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

File Exist

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

VBA Filesystemobject Example 3.1

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

VBA Filesystemobject Example 3.2

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

VBA Filesystemobject Example 3.3

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

Example 3.4

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

Example 3.5

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

Example 3.6

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

Example 3.7

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

File Space

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

VBA Filesystemobject Example 4.1

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

Example 4.2

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

Example 4.3

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

Example 4.4

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
All in One Excel VBA Bundle
500+ Hours of HD Videos
15 Learning Paths
120+ Courses
Verifiable Certificate of Completion
Lifetime Access
Financial Analyst Masters Training Program
2000+ Hours of HD Videos
43 Learning Paths
550+ Courses
Verifiable Certificate of Completion
Lifetime Access
All in One Data Science Bundle
2000+ Hour of HD Videos
80 Learning Paths
400+ Courses
Verifiable Certificate of Completion
Lifetime Access
All in One Software Development Bundle
5000+ Hours of HD Videos
149 Learning Paths
1050+ Courses
Verifiable Certificate of Completion
Lifetime Access
Primary Sidebar
Financial Analyst Masters Training Program2000+ Hours of HD Videos | 43 Learning Paths | 550+ Courses | Verifiable Certificate of Completion | Lifetime Access
All in One Data Science Bundle2000+ Hour of HD Videos | 80 Learning Paths | 400+ Courses | Verifiable Certificate of Completion | Lifetime Access
Footer
About Us
  • Blog
  • Who is EDUCBA?
  • Sign In
  • Live Classes
  • Certificate from Top Institutions
  • Contact Us
  • Verifiable Certificate
  • Reviews
  • Terms and Conditions
  • Privacy Policy
  •  
Apps
  • iPhone & iPad
  • Android
Resources
  • Free Courses
  • Excel Charts
  • Excel Tips
  • All Tutorials
Certification Courses
  • All Courses
  • Excel VBA Course - All in One Bundle
  • VBA Course
  • Excel Data Analysis Course
  • Excel for Marketing Course
  • Excel for Finance Course
  • Excel for HR Training

ISO 10004:2018 & ISO 9001:2015 Certified

© 2023 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

Let’s Get Started

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA

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

EDUCBA
Watch our Demo Courses and Videos

Valuation, Hadoop, Excel, Mobile Apps, Web Development & many more.

By continuing above step, you agree to our Terms of Use and Privacy Policy.
*Please provide your correct email id. Login details for this Free course will be emailed to you

EDUCBA

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

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

Forgot Password?

By signing up, you agree to our Terms of Use and Privacy Policy.

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

Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more