FileSystemObject in VBA – Explained

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:

FileSystemObject Diagram

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

Adding-FSO-reference-to-vba

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

Adding-FileSystemObject-reference-to-vba-3

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

Creating a FSO Object

  • 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:

'Creating a FileSystemObject
Public FSO As New FileSystemObject
Sub DiskSpace()
Dim drv As Drive
Dim Space As Double
Set drv = FSO.GetDrive("C:") ' Creating the the Drive object
Space = drv.FreeSpace
Space = Space / 1073741824 'converting bytes to GB
Space = WorksheetFunction.Round(Space, 2) ' Rounding
MsgBox "C: has free space = " & Space & " GB"
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 and drv.AvailableSpace.

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

Below is the code:

'Creating a FileSystemObject
Public FSO As New FileSystemObject
Sub ChkFolder()
Dim Fldr_name As String
Fldr_name = InputBox("Enter the path of the folder to check :")
If Len(Fldr_name) > 0 Then
  If FSO.FolderExists(Fldr_name) = True Then
    MsgBox "Folder Exists!"
  Else
    FSO.CreateFolder (Fldr_name)
    MsgBox ("Folder Created!")
  End If
Else
  MsgBox "Wrong Input"
End If
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.

Below is the code to accomplish this:
'Creating a FileSystemObject
Public FSO As New FileSystemObject
Sub CopyFolder()
FSO.CopyFolder "C:\Source-Folder\", "D:\Destination-Folder\", True
MsgBox "Copying Done!"
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:

'Creating a FileSystemObject
Public FSO As New FileSystemObject
Sub GetFolderpath()
Dim Windows_Fldr As String
Dim System_Fldr As String
Dim Temp_Fldr  As String
Windows_Fldr = FSO.GetSpecialFolder(0)
System_Fldr = FSO.GetSpecialFolder(1)
Temp_Fldr = FSO.GetSpecialFolder(2)
MsgBox ("Windows folder path = " & Windows_Fldr & vbNewLine & _
"System folder path = " & System_Fldr & vbNewLine & _
"Temp folder path = " & Temp_Fldr)
End Sub

FSO_Example-5

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:

'Creating a FileSystemObject
Public FSO As New FileSystemObject
Sub CreateFile()
Dim txtstr As TextStream
Dim FileName As String
Dim FileContent  As String
Dim File As File
FileName = "C:\TestDirectory\File.txt" 'File to be created
'Creating a file and writing content to it
FileContent = InputBox("Enter the File Content")
If Len(FileContent) > 0 Then
  Set txtstr = FSO.CreateTextFile(FileName, True, True)
  txtstr.Write FileContent
  txtstr.Close
End If
' Reading from the file that we have just created
If FSO.FileExists(FileName) Then
  Set File = FSO.GetFile(FileName)
  Set txtstr = File.OpenAsTextStream(ForReading, TristateUseDefault)
  MsgBox txtstr.ReadAll
  txtstr.Close
' Finally Deleting the File
  File.Delete (True)
End If
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:

'Creating a FileSystemObject
Public FSO As New FileSystemObject
Sub ListFiles()
'Declaring variables
Dim objFolder As Folder
Dim objFile As File
Dim strPath As String
Dim NextRow As Long
'Specify the path of the folder
strPath = "C:\Users\Aniee\Desktop\ExcelTrick\ "
'Create the object of this folder
Set objFolder = FSO.GetFolder(strPath)
'Check if the folder is empty or not
If objFolder.Files.Count = 0 Then
  MsgBox "No files were found...", vbExclamation
  Exit Sub
End If
'Adding Column names for A, B, and C
Cells(1, "A").Value = "File Name"
Cells(1, "B").Value = "Size"
Cells(1, "C").Value = "Modified Date/Time"
'Find the next available row
NextRow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row + 1
'Loop through each file in the folder
For Each objFile In objFolder.Files
'List the name, size, and date/time of the current file
Cells(NextRow, 1).Value = objFile.Name
Cells(NextRow, 2).Value = objFile.Size
Cells(NextRow, 3).Value = objFile.DateLastModified
'Find the next row
NextRow = NextRow + 1
Next objFile
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.

About Ankit Kaul

Ankit is the founder of Excel Trick. He is tech Geek who loves to sit in front of his square headed girlfriend (his PC) all day long. :D. Ankit has a strong passion for learning Microsoft Excel. His only aim is to turn you guys into 'Excel Geeks'.
You can find Ankit on FaceBook or Google Plus

Comments

  1. Rudra sharma says:

    Hi Ankit,
    Thank you very much for explaining these things. Really helpful tips..

  2. I need a VBA code to get the count of files in a particular folder (the hidden files should not be considered). There are multiple folders.

    • Hi Yash, Sorry for replying late. Actually, I was a little busy with other commitments.
      Btw, you can try the following code to count the number of visible files (NOT hidden) in a folder recursively.

      Public FSO As New FileSystemObject
      Sub File_Count()
      Dim Ctr As Integer
      Dim Fl As File
      Dim Number_of_files As Integer
      Number_of_files = 0
      Ctr = 3
      ActiveSheet.Cells(1, 1) = "Folder Path"
      ActiveSheet.Cells(1, 2) = "Files"
      Dim Fldr As Folder
      Set Fldr = FSO.GetFolder("C:\Users\Aniee-Mum-Papa\Desktop\ExcelTrick\")
      For Each Fl In Fldr.Files
      If Fl.Attributes <> 34 Then
      Number_of_files = Number_of_files + 1
      End If
      Next Fl
      ActiveSheet.Cells(2, 1) = Fldr.Path
      ActiveSheet.Cells(2, 2) = Number_of_files
      If Fldr.SubFolders.Count > 1 Then
      Recursive_Count Fldr, Ctr
      End If
      End Sub

      Function Recursive_Count(SFolder As Folder, Ctr As Integer)
      On Error GoTo ErrorHandler
      Dim Number_of_files As Integer
      Dim Sub_Fldr As Folder
      Number_of_files = 0
      For Each Sub_Fldr In SFolder.SubFolders
      For Each Fl In Sub_Fldr.Files
      If Fl.Attributes <> 34 Then
      Number_of_files = Number_of_files + 1
      End If
      Next Fl
      ActiveSheet.Cells(Ctr, 1) = Sub_Fldr.Path
      ActiveSheet.Cells(Ctr, 2) = Number_of_files
      Ctr = Ctr + 1
      If Sub_Fldr.SubFolders.Count > 0 Then
      Recursive_Count Sub_Fldr, Ctr
      End If
      Next Sub_Fldr
      Exit Function
      ErrorHandler:
      MsgBox "Some Error Occurred at " & Sub_Fldr.Path & vbNewLine & "Press OK To continue!"
      End Function

      I have tested this code and it works as expected.
      Please do let me know in case you need any other info from my end :)

  3. Satyanarayana says:

    I got a clear picture of how FSO works.

    Thank you so much for explaning in simple way. Once again thanks a lot Ankit

  4. Shreejit nair says:

    Hi Ankit,
    Can we create a new excel file with FSO. I can see the CreateTextFile method does it mean there is no way to create a new excel file programmatically?

    • Hi Shreejit, This is much simpler and can be done without using FSO.
      You can use the following code to do this:
      Sub AddSaveAsNewWorkbook()
      Dim Wk As Workbook
      Set Wk = Workbooks.Add
      Application.DisplayAlerts = False
      Wk.SaveAs Filename:="C:\MyTestFile.xlsx"
      End Sub

      Please let me know in case you need any other other info from my end.

  5. Hi Ankit,
    In excel, when i click the button it should open the URL in chrome, but the chrome.exe file should not run from the hard code path, it would search and open the chrome. Is it possible by Getfile function.?

    Example i used to open the url in IE browser,
    Sub Button1_Click()
    With CreateObject("InternetExplorer.Application")
    .Visible = True
    .Navigate Worksheets("Sheet1").Range("A9").Value
    End With
    End Sub

    • Hi Mohan, You can do it using the below code:
      Sub Button1_Click()
      URL = "www.exceltrick.com"
      Set objShell = CreateObject("Shell.Application")
      objShell.ShellExecute "chrome.exe", URL, "", "", 1
      End Sub

      I have tested this code and it woks as expected.
      Hope it helps :)

  6. Hi Ankit, Thank your very much for your reply,

    I tried the above code, but i got the following error message.
    —————————
    chrome.exe
    —————————
    Windows cannot find ‘chrome.exe’. Make sure you typed the name correctly, and then try again. To search for a file, click the Start button, and then click Search.
    —————————
    OK
    —————————
    I think ,the system does’t allow to run file from registry.

    The following code is working in my system only, since i gave the direct path
    Sub Button5_Click()
    Dim chromePath As String
    chromePath = “”"C:\Documents and Settings\USERNAME\Local Settings\Application Data\Google\Chrome\Application\chrome.exe”"”
    URL = Range(“A9″).Text
    Shell (chromePath & URL)
    End Sub

    Note: the ‘USERNAME’ is given my system, it won’t for other system.

    Is there any other way to run the ‘chrome.exe’ from the path…?
    Is it possible to search and run form ‘C’drive..?

    • Hi Mohan,
      I tried my code on 2 machines and it ran successfully on both of them.
      May be you are right, your system does’t allow to run files from registry.
      So, as a workaround I would suggest you the following code.
      Sub Button5_Click()
      Dim chromePath As String
      chromePath = "C:\Documents and Settings\" & (Environ$("Username")) & "\Local Settings\Application Data\Google\Chrome\Application\chrome.exe"
      URL = Range("A9").Text
      Shell (chromePath & URL)
      End Sub

      Please note that while giving the path ‘chromePath’ in this code I have used (Environ$("Username")). This will fetch the user name of the person who is currently logged in. And hence the path will be correct for each user.

      Actually, I have suggested you this approach because searching a file (chrome.exe) may consume lot of time and CPU resources and hence may affect the performance.

      Also, do let me know in case you need any other help from my end.

  7. Thank u very much Ankit..

    Its working for me and for other system also.

    The other issue is with firefox. The same code is using to open the URL in firefox. Probably, firefox only is opened but the URL is not triggered.
    The code is:
    Sub Button4_Click()
    Dim firefoxPath As String
    firefoxPath = “”"C:\Program Files\Mozilla Firefox\firefox.exe”"”
    URL = Range(“A1″).Text
    Shell (firefoxPath & URL)
    End Sub

    Please advise, how can i assign the url to FF from Excel sheet.

    • Hi Mohan,

      You’re Welcome! :)
      And, for firefox you can use the below code:

      Sub Button4_Click()
      Dim firefoxPath As String
      firefoxPath = "C:\Program Files\Mozilla Firefox\firefox.exe"
      URL = Range("A1").Text
      Shell (firefoxPath & " -new-tab " & URL)
      End Sub

      Hope it helps :)

  8. Hi Ankit,
    You’re really great.. its working for me Thanks a lot..
    If I need any… will contact you in future..

Speak Your Mind

*

Privacy Policy | Disclaimer | About