The FileSystemObject is used to work with folders and files connected with the system. We can use it to access files, folders, drive, and text streams. It can not only access files, folders and text files but can also create. These operations are not limited to the hard disk of the system but any device connected to the file. It means you can access pen drives, cd roms, or virtually connected network drives.
These are the operations we can do using FileSystemObject in VBA:
For creating, opening, reading, writing and deleting text files.
For adding, altering and deleting folders.
To Iterate through files and folders.
For Copying and moving files or folders to other places.
For checking if a file or folder exists at location or not
How to Access FileSystemObject in VBA?
The file system object is a part of Microsoft Scripting Runtime Library. To access a FileSystemObject we need to connect or add a reference to the Microsoft Scripting Runtime Library or Scrrun.dll.
Note: The FileSystemObject does not support operation of binary files because the Scrrun.dll supports creation and manipulation of files using TextStream Object.
There are two methods for creating FileSystemObject in VBA:
1: Creating FSO object Using CreateObject method:
Using this method we first declare a variable object type. Then set the reference of FSO object to that variable using CreateObject:
Sub LearnFso()
'Creating the object variable
Dim fso As Object
'Create the FileSystemObject using Create Object Method
Set fso = CreateObject("Scripting.FileSystemObject")
Debug.Print fso.GetBaseName("E:\MTR\Feb'18 MTR")
End Sub
This method is dynamic and transferable. It means, if you are sharing the code to other systems, this code will work perfectly fine. It will not matter what version of Microsoft Runtime Scripting version you have.
The only drawback is that you will not be able to see the intellisense provided by VBA. You will have to depend on your knowledge to use all the properties and methods of FileSystemObject.
2: Creating FSO Object by Adding Reference to Microsoft Runtime Scripting
You can directly create a FileSystemObject in VBA using the new keyword. For that you will have to add a reference to the latest Microsoft Scripting Runtime in your system.
To add the reference, go to the references option in the tools menu. Here, find the Microsoft Scripting Runtime dll. Check it and click OK.
Now you are ready to create and use the FSO object.
Sub LearnFso()
Dim fso as new FileSystemObject
Debug.Print fso.GetBaseName("E:\MTR\Feb'18 MTR")
End Sub
Or
Sub LearnFso()
Dim fso as FileSystemObject
Set fso = New FileSystemObject
Debug.Print fso.GetBaseName("E:\MTR\Feb'18 MTR")
End Sub
Both will work fine.
A major advantage of this method is that you will be able to see the intelligence of VBA. VBA will show you all the properties and methods of fso object. It will also tell what kind of variables it will accept and what kind of value it will return.
If you share this code to other systems, you will have to tell them to add reference to Scripting Runtime from tools otherwise they will get a compile error that the user-defined type is not defined. So it is like importing other programming languages.
Note that FSO is not a keyword. You can use it as a variable name. It is just a convention to name filesystemobject as fso. That is why some people confuse it being a keyword.
Both methods of creating FSO objects have their advantages and disadvantages that I have mentioned while explaining them. So use as per your need. In this article, I will use the second method of creating FileSystemObject.
Now that we know how to create a FileSystemObject in VBA, let's use this knowledge in doing some meaningful tasks. I mean let's jump to the examples.
The first example we gonna see is of getting a the sub folder names from a specific folder.
Sub LearnFso() ' decaring variables that we will need Dim fso As FileSystemObject ' Variable for the FileSystemObject Dim fdr As Folder ' Variable for the base folder Dim subfdr As Folder ' Variable for the sub folders Dim fdrpath As String 'to store path of base folder 'Intializing the objects fdrpath = "D:\Downloads" 'Declaring the folder Set fso = New FileSystemObject 'Creating the fso object Set fdr = fso.GetFolder(fdrpath) 'Creating the folder object of given folder 'loop to get all the sub folders name in For Each subfdr In fdr.SubFolders Debug.Print subfdr.Name Next subfdr End Sub
When you run the above code this what you get.
Yeah! Thats my download folder. Don't concentrate on that.
How this worked?
Let's understand in steps:
1: Decaring variables that we will need
Dim fso As FileSystemObject ' Variable for the FileSystemObject
Dim fdr As Folder ' Variable for the base folder
Dim subfdr As Folder ' Variable for the sub folders
First we declared all the variable that we will need in this example. The first variable is of course the fso as file sytem object. The two variables fdr and subfdr are of folder type. We will use the fso object to create a file type object instead of creating directly. The fdrpath is use variable is used to hold the path of the base folder of which we want to get all the sub folders.
2: Intializing the objects
fdrpath = "D:\Downloads" 'Declaring the folder
Set fso = New FileSystemObject 'Creating the fso object
Set fdr = fso.GetFolder(fdrpath) 'Creating the folder object of given folder
In this step we initialized all the objects that we had declared except subfdr. Note that we have intialized the fdr file variable using fso objects's method getFolder.
The GetFolder() method of FileSystemObject take path of a folder or directory as string and returns file type object.
3: loop to get all the sub folders name in the folder object
For Each subfdr In fdr.SubFolders
Debug.Print subfdr.Name
Next subfdr
Here we have used a for each loop to loop through each subfolder in the fdr file object. We used the SubFolders property of the file object to loop.
We use the name property to get the names of each subfolder. And it is done.
To get all the paths or fully qualified names of all the files in a folder and it's subfolder, we need to add a few more lines in example 1 code.
Sub LearnFso() Dim fso As FileSystemObject Dim fdr As Folder ' Variable for the base folder Dim subfdr As Folder ' Variable for the sub folders Dim fdrpath As String 'to store path of base folder Dim fl As File 'to store file object fdrpath = "D:\downloads" Set fso = New FileSystemObject Set fdr = fso.GetFolder(fdrpath) 'loop to get all the sub folders name in For Each subfdr In fdr.SubFolders For Each fl In subfdr.Files 'to loop throgh each file Debug.Print fl.Path 'getting file name Next fl Next subfdr 'to get the files of main folder For Each fl In fdr.Files Debug.Print fl.Path Next fl End Sub
The Folder.Files() method is the method that returns the files in a subfolder. The File.Path() method returns the complete address of the file.
We each have an inner loop to iterate through all the files in the subfolder of the main folder and its.
To get the files of the main folder we use another loop.
Example 3: Save The File Names in a CSV file.
In the previous example we learned how to print the file paths of specific folders on the immediate window. In this example, we will learn how to save those paths in a CSV file. To do so we just need to add a few lines to the code. See the bolde code lines below.
Sub LearnFso() Dim fso As FileSystemObject Dim fdr As Folder ' Variable for the base folder Dim subfdr As Folder ' Variable for the sub folders Dim fdrpath As String 'to store path of base folder Dim fl As File 'to store file object Dim fileList As TextStream 'A textstream object fdrpath = "D:\downloads" Set fso = New FileSystemObject Set fdr = fso.GetFolder(fdrpath) Set fileList = fso.CreateTextFile(fdrpath & "\File List in This Folder.csv", True, False) 'loop to get all the sub folders name in For Each subfdr In fdr.SubFolders For Each fl In subfdr.Files 'to loop through each file fileList.Write fl.Path & "," Next fl Next subfdr 'to get the files of main folder For Each fl In fdr.Files fileList.Write fl.Path & "," Next fl fileList.Close End Sub
Here, we have have declared a new object of FileStream type named fileList
We initialized the filelist variable with a filestream object using the below line.
Set fileList = fso.CreateTextFile(fdrpath & "\File List in This Folder.csv", True, False)
We use the method CreateTextFile of FSO to create a FileStream object. It creates a text file. This method accepts the name of a file with a complete path. The first variable is doing so. We use the .csv extension to create a csv file. The second variable is used to allow overwriting. The third argument is False to declare that it is not a binary file.
In the loops, we replace the debug.print with filelist.Write method to write each file path in the file created.
So yeah guys, this is how you can use the FileSystemObject. The FSO can be used for many more things, that we will discuss in the upcoming articles. If you have any doubts about this article of FSO related query, ask me in the comment section below.
Related Articles:
Getting Started With Excel VBA UserForms| I will explain how to create a form in excel, how to use VBA toolbox, how to handle user inputs and finally how to store the user inputs. We will go through these topics using one example and step by step guide.
VBA variables in Excel| VBA stands for Visual Basic for Applications. It is a programming language from Microsoft. It is used with Microsoft Office applications such as MSExcel, MS-Word and MS-Access whereas VBA variables are specific keywords.
Excel VBA Variable Scope| In all the programming languages, we have variable access specifiers that define from where a defined variable can be accessed. Excel VBA is no Exception. VBA too has scope specifiers.
ByRef and ByVal Arguments | When an argument is passed as a ByRef argument to a different sub or function, the reference of the actual variable is sent. Any changes made into the copy of the variable, will reflect in the original argument.
Delete sheets without confirmation prompts using VBA in Microsoft Excel | Since you are deleting sheets using VBA, you know what you are doing. You would like to tell Excel not to show this warning and delete the damn sheet.
Add And Save New Workbook Using VBA In Microsoft Excel 2016| In this code, we first created a reference to a workbook object. And then we initialized it with a new workbook object. The benefit of this approach is that you can do operations on this new workbook easily. Like saving, closing, deleting, etc
Display A Message On The Excel VBA Status Bar| The status bar in excel can be used as a code monitor. When your VBA code is lengthy and you do several tasks using VBA, you often disable the screen update so that you don’t see that screen flickering.
Turn Off Warning Messages Using VBA In Microsoft Excel 2016| This code not only disables VBA alerts but also increases the time efficiency of the code. Let’s see how.
Popular Articles:
50 Excel Shortcuts to Increase Your Productivity | Get faster at your task. These 50 shortcuts will make you work even faster on Excel.
The VLOOKUP Function in Excel | This is one of the most used and popular functions of excel that is used to lookup value from different ranges and sheets.
COUNTIF in Excel 2016 | Count values with conditions using this amazing function. You don't need to filter your data to count specific values. Countif function is essential to prepare your dashboard.
How to Use SUMIF Function in Excel | This is another dashboard essential function. This helps you sum up values on specific conditions.
The applications/code on this site are distributed as is and without warranties or liability. In no event shall the owner of the copyrights, or the authors of the applications/code be liable for any loss of profit, any problems or any damage resulting from the use or evaluation of the applications/code.
