How to use FileSystemObject in Excel VBA

FileSystemObject can be found inside the Scrrun.dll file, a Microsoft runtime scripting library that supports the creation and manipulation of files using the TextStreamObject (another object in the library).

Note: The FileSystemObject does not support Binary Files, that is, you cannot do any operations on a binary file using this object.

------------------

Here’s a list of articles in this blog that explains how to use the FileSystemObject for various operations.

1) Quickly get or extract filenames from FilePaths in Excel using VBA Macro: Let’s assume, you have a list of filepaths in one of the columns in your excel sheet and you want extract only filenames from each given file path and write the name of the file in the next column.

2) How to copy or move files from one folder to another in Excel using VBA: The example in this articles explains how easily you can move files across various folders using the methods from VBAs FileSystemObject.

------------------

Create FSO Object

You can create an FSO object in your VBA application using two different methods (simple methods, of course). First, by adding a reference of the Microsoft Scripting Library and second by using the CreateObject() method. I’ll show you how to use both the methods.

1) Add Microsoft Scripting Library Reference

The simplest way to create an FSO object is by referencing the source of the object in your application, the Microsoft Scripting Library. It provides all the methods and properties that you will need for file manipulation.

You have to add its reference, since this object is not included (like other objects) in the object library for Visual Basic Applications.

Here are the steps to add the reference.

1) Open an Excel file and save the file in .xlsm format (the macro format).
2) Press Ctrl+F11 to open the VBA editor. You can also right click Sheet1 and select View Code option to open the editor.
3) Open Project Explorer and double click Sheet1. Since we’ll create our FSO object in Sheet1. You can write it anywhere in your application, depending upon your requirement.
4) From the top menu, choose Tools –> References…. This will open the reference window.
5) Find Microsoft Scripting Runtime from the list, select it and press OK.

After you have added the above reference, it you will see that the object is now available in your VBA application.

Let's see an example.

I have a folder named myPhotoAlbum inside my computers D: drive and I want to check if it exists. Here’s how I’ll do it,

Option Explicit

Sub FSO_Example()
    Dim fso As New FileSystemObject     ' Create the object.
    
    Dim sFolder As String
    sFolder = "D:\myPhotoAlbum"         ' The folder I wish to check if exists.
    
    If fso.FolderExists(sFolder) Then      ' Check if the folder exists.
        Debug.Print "Folder exists"
    Else
        Debug.Print "Folder does not exists"
    End If
End Sub

Out of the many methods and properties that comes with the FileSystemObject, I am using only one method in this example, the FolderExists() method. The method takes a parameter, the full path along with the folder name and checks if it exists. It returns a Boolean value (True or False).

Run the application. See the immediate window (press Ctrl+G in your VBA editor) for result.

Now, if the folder exists in the drive, let’s see if it has files. If yes, get the names of the files.

So, please add some files (any file with any extension) in the folder and add this code.

Option Explicit

Sub FSO_Example()
    Dim fso As New FileSystemObject     ' Create the object.
    
    Dim sFolder, sFile As String
    sFolder = "D:\myPhotoAlbum"         ' The folder I wish to check if exists.
    
    If fso.FolderExists(sFolder) Then       ' Check if the folder exists.
        
        ' Create a folder object.
        Dim folder As folder
        Set folder = fso.GetFolder(sFolder)
        
        If folder.Files.Count > 0 Then     ' See if the folder has files.
            Dim file As file
            For Each file In folder.Files       ' Get all the files.
                Debug.Print file.Name           ' Show the file name.
                Debug.Print file.Size           ' Show file size.
            Next file
        End If
    Else
        Debug.Print "Folder does not exists"
    End If
End Sub

2) Using CreateObject() Method to Create FSO Object

You can create the FileSystemObject and avail its properties by using the CreateObject() method. In this procedure, you do not have to add any reference, such as the one I have explained in my first example.

Here’s how I’ll create the FileSystem object using the CreateObject() method.

Option Explicit

Sub FSO_Example()
    Dim fso
    Set fso = CreateObject("scripting.filesystemobject")
    
    Dim sFolder As String
    sFolder = "D:\books"                   ' The folder where I’ll create the text file.
    
    If fso.FolderExists(sFolder) Then      ' Check if the folder exists.
        
        Dim txtStream
        Set txtStream = fso.CreateTextFile(sFolder & "/sample.txt")    ' Create the file.
        txtStream.Write "Hello, I am Arun Banik"       ' Write into the file.
        
        Set txtStream = fso.OpenTextFile(sFolder & "/sample.txt")      ' Open the file.

        ' Loop though the file and read its contents.
        Do While Not txtStream.AtEndOfStream           
            Debug.Print txtStream.ReadLine
        Loop
        
        txtStream.Close        ' Close the file.
    Else
        Debug.Print "Folder does not exists"
    End If
End Sub

In this example, I have used some of the methods that come with the FSO object, to create a text file (.txt file) in a folder. In-addition, I am writing some contents in the file and finally reading the contents of the file.

It's so simple and you can perform the same operation by adding a reference. However, there is a problem using the CreateObject() method approach. The intelliSense (the code completion tool that VBA provides) won’t work with this method, that is, it will not show you the methods that comes with the object. You’ll have to look the methods elsewhere, like Google it or find the methods in your MSDN file.

Here’s an example (advanced example) that explains how you can use the FileSystemObject using the CreateObject() method to copy or move files across folders.

Creating a file system object in VBA is simple. The best procedure to create FileSystemObject in VBA is to add a reference of Microsoft Scripting Library to your application and you can easily get access to its properties. If in case you do not find the reference, check if the file Scrrun.dll exists where you have installed Ms-Office. Or else reinstall Ms-Office your computer.

← PreviousNext →