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.

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

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

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

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

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.

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

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.

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

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

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

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

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

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

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.

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

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.

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

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

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.

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.

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.