FileSystemObject in VBA – Explained

http://www.exceltrick.com/formulas_macros/filesystemobject-in-vba/

FileSystemObject also called as FSO, provides an easy object based model to access computer’s file system. You simply have to create an instance of FileSystemObject in VBA and then you can generate files, read files, delete files, iterate though folders and do many other operations on your computer’s file system.

The FileSystemObject is present inside the Microsoft Scripting Runtime Library i.e. Scrrun.dll. This DLL supports the creation and manipulation of files using TextStream object and this is the reason why FSO doesn’t supports operation on binary files.

Uses of File System Object:

FileSystemObject can be used for multiple tasks such as:

• Creating, opening, reading, writing and deleting text files.
• Creating, altering and deleting folders.
• Iterating files and folders.
• Copying and moving files or folders.
• Checking if a location (file path) exists or not.

Accessing FileSystemObject:

The FileSystemObject comprises of several object collections and each of these object collections contain information about a specific aspect of file system. These collections are:

Object	Description
Drive	This object allows you to get information about a drive attached to your system. For instance it can tell you the space available in a drive, its logical name etc. Note that the drive object that we are referring here doesn’t necessarily means the hard disk, it can be a CD-ROM, Flash drive, RAM or even a logically connected drive via LAN.
Folder	This object allows you to create, delete, move or query a folder hierarchy.
File	This object allows you to create, delete, move or query a File.
TextStream	TextStream object allows you to read or write text files.

So, diagrammatically a FileSystemObject can be shown as:

Please note that FSO communicates with most of the above objects indirectly. It just directly contains the object collection for “Drives”. Each “Drive” object in the “Drives” collection contains a chain of “Folder” objects. And each “Folder” object contains a “File” collection.

To make it more meaningful, let’s say it this way: A FileSystemObject contains three main methods to fetch (read) specific information about Drives, Folders or Files, these methods are GetDrive, GetFolder andGetFile respectively. Now let’s say you need to find the file size of a particular file, so you will create an instance of the GetFile method and then fetch its size property.

Note: GetDrive, GetFolder and GetFile are not the only methods inside FSO, I have just used them to explain things. In the below section we will see all the methods contained inside FileSystemObject.

FSO Methods:

Below table gives details about various FileObjectSystem methods and the tasks they perform:

Method	Description
GetDrive, GetFolder and GetFile	These methods are used for fetching information about Drive, Folders and Files respectively.
CreateFolder and CreateTextFile	Helps in creating new folders or files.
DeleteFile and DeleteFolder	Helps in deleting exiting files or folders.
CopyFile and CopyFolder	These methods help in copying files or folders from one location to another.
MoveFile and MoveFolder	These methods help in moving files or folders from one location to another.

Creating a FileSystemObject in VBA:

In this section we will deal with two things:

• Creating a reference of Microsoft Scripting Runtime Library i.e. Scrrun.dll
• Creating an FSO Object.

Creating a reference of Microsoft Scripting Runtime Library:

To create a reference of Microsoft Scripting Runtime Library follow the below steps:

• First of all open the VBA editor by pressing “Alt + F11”.

• Next, navigate to “Tools” > “Reference” as shown above.

• This will open a references window. Here select and check the entry “Microsoft Scripting Runtime” and click “OK”.

• Now the reference to Microsoft Scripting Runtime Library has been added.

Creating a FSO Object:

Creating a FSO object is simple, follow the below steps to do this:

• In the VBA editor navigate to “Insert” > “Module”.

• Now in the module window type “Public FSO As New FileSystemObject”.

• This will create an object of FileSystemObject with the name FSO.

After this you can simply access the FileSystemObject’s methods using the FSO object.

6 Practical Examples of accessing FileSystemObject to perform different tasks:

Now let’s move to some practical examples of FSO:

Example 1: Use FSO to find the total free space of a drive.

Below is the code to do this:

1. 'Creating a FileSystemObject  
2. Public FSO As New FileSystemObject  
3. Sub DiskSpace()  
4. Dim drv As Drive  
5. Dim Space As Double  
6. Set drv = FSO.GetDrive("C:") ' Creating the the Drive object  
7. Space = drv.FreeSpace  
8. Space = Space / 1073741824 'converting bytes to GB  
9. Space = WorksheetFunction.Round(Space, 2) ' Rounding  
10. MsgBox "C: has free space = " & Space & " GB"  
11. End Sub  

Explanation:

In this code first we have created a Drive object using ‘GetDrive’ Method and then we have used its ‘FreeSpace’ property to fetch the free space. Finally we have displayed the free space using a message box.

Note that there are two properties to fetch the free space of a drive i.e. drv.FreeSpace anddrv.AvailableSpace.

Example 2: Check if a Folder exists or not. If the folder doesn’t exists then create that folder.

Below is the code:

view plaincopy to clipboardprint?

1. 'Creating a FileSystemObject  
2. Public FSO As New FileSystemObject  
3. Sub ChkFolder()  
4. Dim Fldr_name As String  
5. Fldr_name = InputBox("Enter the path of the folder to check :")  
6. If Len(Fldr_name) > 0 Then  
7.   If FSO.FolderExists(Fldr_name) = True Then  
8.     MsgBox "Folder Exists!"  
9.   Else  
10.     FSO.CreateFolder (Fldr_name)  
11.     MsgBox ("Folder Created!")  
12.   End If  
13. Else  
14.   MsgBox "Wrong Input"  
15. End If  
16. End Sub  

Explanation:

In the code we have used an InputBox function to get the path of folder from the user. After this using If statement along with FolderExists method we have checked whether that folder is present or not. If the folder is not present then we create that folder using the CreateFolder method.

Note: CreateFolder method will only create a single folder at a time. So, if you supply an argument “C:\Folder1\Folder2\Folder3” to it then it will only create the Folder3 inside Folder2. But if Folder2 doesn’t exist then it will throw a path not found error.

Example 3:  Write a code using FSO to copy a Folder from one location to another.

view plaincopy to clipboardprint?

1. Below is the code to accomplish this:  
2. 'Creating a FileSystemObject  
3. Public FSO As New FileSystemObject  
4. Sub CopyFolder()  
5. FSO.CopyFolder "C:\Source-Folder\", "D:\Destination-Folder\", True 
6. MsgBox "Copying Done!"  
7. End Sub  

Explanation:

In the code we have used the CopyFolder method of FSO, this method accepts three arguments:

• Source Path
• Destination path
• A Boolean argument to specify Overwrite Existing.

Example 4: Using FileSystemObject fetch the Temp directory, System folder and Windows folder.

To do this we can use the below code:

view plaincopy to clipboardprint?

1. 'Creating a FileSystemObject  
2. Public FSO As New FileSystemObject  
3. Sub GetFolderpath()  
4. Dim Windows_Fldr As String  
5. Dim System_Fldr As String  
6. Dim Temp_Fldr  As String  
7. Windows_Fldr = FSO.GetSpecialFolder(0)  
8. System_Fldr = FSO.GetSpecialFolder(1)  
9. Temp_Fldr = FSO.GetSpecialFolder(2)  
10. MsgBox ("Windows folder path = " & Windows_Fldr & vbNewLine & _  
11. "System folder path = " & System_Fldr & vbNewLine & _  
12. "Temp folder path = " & Temp_Fldr)  
13. End Sub  

Explanation:

In the code we have used the GetSpecialFolder method of FSO, this method accepts a single numerical argument i.e. 0-2.

• FSO.GetSpecialFolder(0) – Fetches the path of Windows Folder.
• FSO.GetSpecialFolder(1) – Fetches the path of System Folder.
• FSO.GetSpecialFolder(2) – Fetches the path of Local Temporary folder.

Example 5: Create a text file, write some content to it, then read the file and finally delete the file.

Below is the code to accomplish this:

view plaincopy to clipboardprint?

1. 'Creating a FileSystemObject  
2. Public FSO As New FileSystemObject  
3. Sub CreateFile()  
4. Dim txtstr As TextStream  
5. Dim FileName As String  
6. Dim FileContent  As String  
7. Dim File As File  
8. FileName = "C:\TestDirectory\File.txt" 'File to be created  
9. 'Creating a file and writing content to it  
10. FileContent = InputBox("Enter the File Content")  
11. If Len(FileContent) > 0 Then  
12.   Set txtstr = FSO.CreateTextFile(FileName, True, True)  
13.   txtstr.Write FileContent  
14.   txtstr.Close  
15. End If  
16. ' Reading from the file that we have just created  
17. If FSO.FileExists(FileName) Then  
18.   Set File = FSO.GetFile(FileName)  
19.   Set txtstr = File.OpenAsTextStream(ForReading, TristateUseDefault)  
20.   MsgBox txtstr.ReadAll  
21.   txtstr.Close  
22. ' Finally Deleting the File  
23.   File.Delete (True)  
24. End If  
25. End Sub  

Explanation:

This code first creates a text file, adds content to it, then reads it, displays it using a message box and finally deletes that file.

Example 6: Write a VBA Code that can iterate all the files present inside a folder and also fetches their size and modified date.

Following code can accomplish this task:

view plaincopy to clipboardprint?

1. 'Creating a FileSystemObject  
2. Public FSO As New FileSystemObject  
3. Sub ListFiles()  
4. 'Declaring variables  
5. Dim objFolder As Folder  
6. Dim objFile As File  
7. Dim strPath As String  
8. Dim NextRow As Long  
9. 'Specify the path of the folder  
10. strPath = "C:\Users\Aniee\Desktop\ExcelTrick\ "  
11. 'Create the object of this folder  
12. Set objFolder = FSO.GetFolder(strPath)  
13. 'Check if the folder is empty or not  
14. If objFolder.Files.Count = 0 Then  
15.   MsgBox "No files were found...", vbExclamation  
16.   Exit Sub  
17. End If  
18. 'Adding Column names for A, B, and C  
19. Cells(1, "A").Value = "File Name"  
20. Cells(1, "B").Value = "Size"  
21. Cells(1, "C").Value = "Modified Date/Time"  
22. 'Find the next available row  
23. NextRow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row + 1  
24. 'Loop through each file in the folder  
25. For Each objFile In objFolder.Files  
26. 'List the name, size, and date/time of the current file  
27. Cells(NextRow, 1).Value = objFile.Name  
28. Cells(NextRow, 2).Value = objFile.Size  
29. Cells(NextRow, 3).Value = objFile.DateLastModified  
30. 'Find the next row  
31. NextRow = NextRow + 1  
32. Next objFile  
33. End Sub  

Explanation:

In this code we have created a Folder object and then we are iterating all its files using For Each loop. From the file object we are fetching the Name, Size and DateLastModified properties and finally we are writing them to the active worksheet.

So, this was all about FileSystemObject and how to use it in VBA. Do share your comments related to the topic.