Skip to content
Home » Excel VBA File Management Using The FileSytemObject

Excel VBA File Management Using The FileSytemObject

    Using the FileSytemObject

    The FileSystemObject (FSO) provides a useful way to access the filing system on your computer and network drives.  To use the FSO you first need to create a reference to the Microsoft Scripting Runtime Library.

    In the VBE click Tools | References.

    Check the option Microsoft Scripting Runtime and click OK.

    References Excel VBA

    You can browse the scripting objects, methods and properties using the Object Browser.  Select Scripting from the All Libraries drop down.

    Select the Scripting Library

    The FileSystemObject contains lots of useful methods that you can use for drives, folders and files.

    FileSystemObject Methods and Propertiess

    To use the File System Object you need to create a new instance of it as shown in the code below.

    'Create a new instance of the File System Object
    Dim fso As Scripting.FileSystemObject
    Set fso = New Scripting.FileSystemObject

    Get Methods

    Get methods allow you to retrieve information about a drive, folder or file.

    Get Drive

    Use GetDrive to specify which drive you want to examine.  With the drive specified you can retrieve information about it using the properties shown in the Object Browser below.

    Drive Properties

    In this example we use the FreeSpace property of the Drive object to calculate the available space on a drive.

    Sub UsingGetDrive()
    Const BytesToTB As Double = 1099511627776#
    Dim fso As Scripting.FileSystemObject
    Set fso = New Scripting.FileSystemObject
    Dim drv As Scripting.Drive
    Set drv = fso.GetDrive("D:")
    Dim AvlbSpace As Double
    AvlbSpace = Round(drv.FreeSpace / BytesToTB, 2)
    MsgBox "Drive " & drv.DriveLetter & " has " & AvlbSpace & "TBs of free space."
    End Sub

    The code above creates the message box shown below.

    MsgBox

    GetFolder

    Use GetFolder to specify which folder you want to examine.  With the folder specified you can retrieve information about it using the properties shown in the Object Browser below.

    Folder properties

    In this example we use the Files property to allow us to count the number of files present in a folder.

    Sub UsingGetFolder()
    Dim fso As Scripting.FileSystemObject
    Set fso = New Scripting.FileSystemObject
    Dim fld As Scripting.Folder
    Set fld = fso.GetFolder("C:\Users\xxxx\Desktop\MyFolder")
    Dim CountFiles As Byte
    CountFiles = fld.Files.Count
    MsgBox "There are " & CountFiles & " files in folder: " & fld.Name
    End Sub

    The code above creates the message box shown below.

    MsgBox

    GetFile

    Use GetFile to specify which file you want to examine.  With the file specified you can retrieve information about it using the properties shown in the Object Browser below.

    File Properties & Methods

    In this example we use the DateLastModified property of the file object.

    Sub UsingGetFile()
    Dim fso As Scripting.FileSystemObject
    Set fso = New Scripting.FileSystemObject
    Dim fle As Scripting.File
    Set fle = fso.GetFile("C:\Users\xxxx\Desktop\MyFolder\Budget.xlsx")
    Dim LstMod As Date
    LstMod = fle.DateLastModified
    MsgBox fle.Name & " was last modified on " & LstMod
    End Sub

    The code above creates the message box shown below.

    Other Useful Methods

    CreateFolder Method

    The CreateFolder method has a single parameter: Path

    Sub CreateAFolder()
    Dim fso As Scripting.FileSystemObject
    Set fso = New Scripting.FileSystemObject
    Dim MyPath As String
    MyPath = "C:\Users\xxxx\Desktop\"
    fso.CreateFolder MyPath & "FolderXYZ"
    End Sub

    CopyFile Method

    The CopyFile method has three parameters: Source, Designation and OverWriteFiles (defaults to TRUE)

    Sub CopyAFile()
    Dim fso As Scripting.FileSystemObject
    Set fso = New Scripting.FileSystemObject
    Dim MyPath As String
    MyPath = "C:\Users\xxxx\Desktop\"
    fso.CopyFile Source:=MyPath & "MyFolder\Budget.xlsx", Destination:=MyPath & "FolderXYZ\Budget.xlsx"
    End Sub

    Note that you have to state the file name in the Destination parameter.

    DeleteFile Method

    The DeleteFile method has two parameters FileSpec and Force  (delete if Read-only defaults to FALSE)

    Sub DeleteFile()
    Dim fso As Scripting.FileSystemObject
    Set fso = New Scripting.FileSystemObject
    Dim MyPath As String
    MyPath = "C:\Users\xxxx\Desktop\"
    fso.DeleteFile MyPath & "MyFolder\Junk.xlsx"
    End Sub

    MoveFile Method

    The MoveFile method has two parameters: Source and Destination.

    Sub MoveFile()
    Dim fso As Scripting.FileSystemObject
    Set fso = New Scripting.FileSystemObject
    Dim MyPath As String
    MyPath = "C:\Users\xxxx\Desktop\"
    fso.MoveFile _
        Source:=MyPath & "MyFolder\Move Me.xlsx", Destination:=MyPath & "FolderXYZ\Move Me.xlsx"
    End Sub

    FolderExists Method

    The FolderExists method has a single parameter: FolderSpec

    Sub CheckIfFolderExists()
    Dim fso As Scripting.FileSystemObject
    Set fso = New Scripting.FileSystemObject
    Dim MyPath As String
    MyPath = "C:\Users\xxxx\Desktop\"
    'Check to see if folder already exists
    If fso.FolderExists(MyPath & "\MyFolder") Then
        MsgBox "Folder Already Exists"
    Else
        'Create a folder
        fso.CreateFolder MyPath & "New Folder"
    End If
    End Sub

    FileExists Method

    The FileExists method has a single parameter: FileSpec

    Sub CheckIfFileExists()
    Set fso = New Scripting.FileSystemObject
    Dim MyPath As String
    MyPath = "C:\Users\chest\Desktop\"
    'Check to see if file exists
    If fso.FileExists(MyPath & "MyFolder\Move Me.xlsx") Then
        'Move a file
        fso.MoveFile _
            Source:=MyPath & "MyFolder\Move Me.xlsx", _
            Destination:=MyPath & "FolderXYZ\Move Me.xlsx"
    Else
        MsgBox "File Does Not Exist"
    End If
    End Sub

    Practical Examples

    Copy Files With a Specific File Type

    In this example the Type property of the File object is used to determine whether a file gets copied. We only want to copy .xlsx files.  Contents of MyFolder shown below.

    Files of Different File Types

    Sub CopyFilesWithASpecificFileType()
    
    Dim fso As Scripting.FileSystemObject
    Set fso = New Scripting.FileSystemObject
    
    Dim fle As Scripting.File
    
    Dim MyFolderPath As String
    MyFolderPath = "C:\Users\chest\Desktop\MyFolder"
    
    Dim ExcelFolderPath As String
    ExcelFolderPath = "C:\Users\chest\Desktop\ExcelFolder"
    
    Dim MyFolder As Scripting.Folder
    Set MyFolder = fso.GetFolder(MyFolderPath)
    
    For Each fle In MyFolder.Files
        If fle.Type = "Microsoft Excel Worksheet" Then fle.Copy ExcelFolderPath & "\" & fle.Name
    Next fle
    End Sub

    Contents of ExcelFolder (once code executed) shown below.

    Excel Files

    Copy All Excel File Types

    The previous example would only have copied .xlsx files.  What if we want to copy all Excel file types: xlsx, xls, and xlsm. Contents of MyFolder shown below.

    Files of Different File Types

    We can return the file type using the Scripting FileSystemObject method GetExtensionName

    The first two characters of an Excel file extension are xl. Using the Left function we can determine if this is the case.

    If Left(fso.GetExtensionName(fle.Path), 2) = "xl" Then
        fle.Copy ExcelFolderPath & "\" & fle.Name
    End If

    We can then loop this code for each file in our folder.

    Sub CopyAllExcelFilesIntoFolder()
    Dim fso As Scripting.FileSystemObject
    Set fso = New Scripting.FileSystemObject
    
    Dim fle As Scripting.File
    
    Dim MyFolderPath As String
    MyFolderPath = "C:\Users\chest\Desktop\MyFolder"
    
    Dim ParentPath As String
    ParentPath = "C:\Users\chest\Desktop\"
    
    Dim MyFolder As Scripting.Folder
    Set MyFolder = fso.GetFolder(MyFolderPath)
    
    Dim ExcelFolderPath As String
    ExcelFolderPath = fso.CreateFolder(ParentPath & "All Excel Files").Path
    
    For Each fle In MyFolder.Files
     If Left(fso.GetExtensionName(fle.Path), 2) = "xl" Then
         fle.Copy ExcelFolderPath & "\" & fle.Name
     End If
    Next fle
    End Sub

    Contents of All Excel Files folder (once code executed) shown below.

    Organise Files Based on File Type

    In this example we create a folder for each file type of files found in MyFolder and then copy the files to the appropriate folder.

    Contents of MyFolder shown below.

    Files of Different File Types

    Sub OrganiseIntoFoldersBasedOnFileType()
    Dim fso As Scripting.FileSystemObject
    Set fso = New Scripting.FileSystemObject
    
    Dim fle As Scripting.File
    
    'Store the old folder's path in a variable
    Dim MyFolderPath As String
    MyFolderPath = "C:\Users\chest\Desktop\MyFolder"
    
    'Store the path for the new folders in a variable
    Dim ParentPath As String
    ParentPath = "C:\Users\chest\Desktop\"
    
    'Create a scripting folder object
    Dim MyFolder As Scripting.Folder
    Set MyFolder = fso.GetFolder(MyFolderPath)
    
    'Loop through the collection of files in the old folder
    For Each fle In MyFolder.Files
    'If a folder for the file type does not exist...
        If Not fso.FolderExists(ParentPath & fle.Type) Then
        '...then create a folder
        fso.CreateFolder ParentPath & fle.Type
        End If
        'Copy the file to the correct folder
        fle.Copy ParentPath & fle.Type & "\" & fle.Name
    Next fle
    
    End Sub

    Resulting folders shown below.

    Files in Folders

    Organise Files Based on File Name

    In this example we want to organise files based on file name.  We want a folder for each customer: HYT and XYZ and within the customer folder we want a subfolder for the month that each file relates to.

    Sub OrganiseFilesBasedOnFileName()
    Dim fso As Scripting.FileSystemObject
    Set fso = New Scripting.FileSystemObject
    
    Dim fle As Scripting.File
    
    'Store the all files folder's path in a variable
    Dim AllFilesFolderPath As String
    AllFilesFolderPath = "C:\Users\chest\Desktop\All Files"
    
    'Store the path for the new folders in a variable
    Dim ParentPath As String
    ParentPath = "C:\Users\chest\Desktop\"
    
    'Create a scripting folder object
    Dim AllFilesFolder As Scripting.Folder
    Set AllFilesFolder = fso.GetFolder(AllFilesFolderPath)
    
    'Loop through the collection of files in the old folder
    
    For Each fle In AllFilesFolder.Files
        'Check to see if a folder exists
        'for the customer that the file relates to
        If Not fso.FolderExists(ParentPath & Left(fle.Name, 3)) Then
            'If it doesn't create a folder
            'based on the initials of the customer name
            fso.CreateFolder ParentPath & Left(fle.Name, 3)
        End If
        'Check to see if there is subfolder within the company
        'folder for the month that the file relates to
        If Not fso.FolderExists(ParentPath & Left(fle.Name, 3) & "\" & _
            MonthName(Mid(fle.Name, _
            WorksheetFunction.Find(" ", fle.Name, _
            WorksheetFunction.Find(" ", fle.Name) + 1) + 4, 2))) Then
            'If there isn't create the subfolder
            fso.CreateFolder ParentPath & Left(fle.Name, 3) & "\" & _
            MonthName(Mid(fle.Name, _
            WorksheetFunction.Find(" ", fle.Name, _
            WorksheetFunction.Find(" ", fle.Name) + 1) + 4, 2))
        End If
        'Copy the file to the relevant subfolder
        fle.Copy ParentPath & Left(fle.Name, 3) & _
        "\" & MonthName(Mid(fle.Name, _
        WorksheetFunction.Find(" ", fle.Name, _
        WorksheetFunction.Find(" ", fle.Name) + 1) + 4, 2)) & "\" & fle.Name
            
    Next fle
    
          
    End Sub

    Sub folders with the HYT customer folder.

    Contents of the September sub folder.