FileSystemObject on VBA


To totally unlock this section you need to Log-in


Login

The Scripting.FileSystemObject Object (FSO) is a useful tool for performing many file system tasks. There are two ways to create the FSO Object — early and late binding.

Early Binding

First, set a reference to the FSO Object Library. In the VBA Editor, go to Tools » References and select "Microsoft Scripting Runtime". You can also click "Browse" and go to %windir%\system32\scrrun.dll.

FileSystemObject on VBA

Then declare your FSO object like this:

Dim fso As Scripting.FileSystemObject

Late Binding

Declare your FSO object like this:

Dim fso As Object

Regardless of the binding method, to instantiate the object you would write

Set fso = CreateObject("Scripting.FileSystemObject")

You can also use a function to create the FSO object:

Function GetFSO() As Object ' Scripting.FileSystemObject

On Error Resume Next
Set GetFSO = CreateObject("Scripting.FileSystemObject")
End Function

Call the function like this:

Sub TestFSO()

Dim fso As Object ' Scripting.FileSystemObject
Set fso = GetFSO
' must test for nothing due to On Error Resume Next statement
If fso Is Nothing Then
Exit Sub
End If
End Sub

As usual, we recommend late binding to avoid having to remember to manually set a reference to FSO by going to Tools » References.

CopyFile Method

This method copies one or more files from one location to another. The file must be closed. Trying to move or copy an open file can lead to unpredictable results. CopyFile accepts wildcards, which means you can copy groups of files. Example:

Sub TestFSO()

Dim fso As Object ' Scripting.FileSystemObject
Set fso = GetFSO
If fso Is Nothing Then
Exit Sub
End If
' copyfile example
fso.CopyFile "C:\My Files\*.xlsx", "C:\My Backup Folder\"
End Sub

This method can be encapsulated into its own procedure:

Sub FSOCopyFile(fso As Object, sourceFolder As String, _

destinationFolder As String, Optional overwrite As Boolean = True)
fso.CopyFile sourceFolder, destinationFolder, overwrite
End Sub

Our example then becomes:

Sub TestFSO()

Dim fso As Object ' Scripting.FileSystemObject
Set fso = GetFSO
If fso Is Nothing Then
Exit Sub
End If
' copyfile example
FSOCopyFile fso, "C:\My Files\*.xlsx", "C:\My Backup Folder\"
End Sub

CreateFolder Method

The CreateFolder method creates a folder and returns it as a Scripting.Folder Object.

fso.CreateFolder "C:\New folder for files\"

If you need to do something further with the folder, return it to a Folder Object like this:

Sub TestFSO()

Dim fso As Object ' Scripting.FileSystemObject
Dim fldr As Object ' Scripting.Folder
Set fso = GetFSO
If fso Is Nothing Then
Exit Sub
End If
' createfolder example
Set fldr = fso.CreateFolder("C:\New folder for files\")
End Sub

An encapsulated method would look like this:

Function FSOCreateFolder(fso As Object, folderName As String) As Object ' Scripting.Folder

On Error Resume Next
Set FSOCreateFolder = fso.CreateFolder(folderName)
End Function

Our example procedure would then become:

Sub TestFSO()

Dim fso As Object ' Scripting.FileSystemObject
Dim fldr As Object ' Scripting.Folder
Set fso = GetFSO
If fso Is Nothing Then
Exit Sub
End If
' createfolder example
Set fldr = FSOCreateFolder(fso, "C:\New folder for files\")
If fldr Is Nothing Then
MsgBox "could not create folder"
End If
End Sub

CreateTextFile Method

This method lets you read or write text files.

fso.CreateTextFile("C:\my files\dummyfile.txt", True)

You can also use this method with a Folder Object.

fldr.CreateTextFile("C:\my files\dummyfile.txt", True)

This method returns a TextStream Object which you use to read or write to the file. Example:

Sub TestFSO()

Dim fso As Object ' Scripting.FileSystemObject
Dim fldr As Object ' Scripting.Folder
Dim txtstr As Object ' Scripting.TextStream
Set fso = GetFSO
If fso Is Nothing Then
Exit Sub
End If
Set txtstr = fso.CreateTextFile("C:\my files\dummyfile.txt", True)
' or
Set fldr = fso.GetFolder("C:\my files\")
Set txtstr = fldr.CreateTextFile("C:\my files\dummyfile.txt", True)
End Sub

This method can also be encapsulated as follows:

Function FSOCreateTextFile(obj As Object, filePath As String, Optional overwrite As Boolean) As Object ' Scripting.TextStream

' only Scripting.Folder and Scripting.FileSystemObject use CreateTextFile method
If TypeName(obj) = "Folder" Or TypeName(obj) = "FileSystemObject" Then
On Error Resume Next
Set FSOCreateTextFile = obj.CreateTextFile(filePath, overwrite)
End If
End Function

You would call the method like this:

Set txtstr = FSOCreateTextFile(fso, "C:\my files\dummyfile.txt", True)

DeleteFile Method

The DeleteFile method is used to delete files. Like the CopyFile method, this method accepts wildcards for deleting multiple matching files.

Sub TestFSO()

Dim fso As Object ' Scripting.FileSystemObject
Set fso = GetFSO
If fso Is Nothing Then
Exit Sub
End If
fso.DeleteFile "C:\my files\*.docx"
End Sub

This would delete all of the .docx files in the C:\my files\ folder. We can also encapsulate this function:

Sub FSODeleteFile(fso As Object, filespec As String, Optional forceDelete As Boolean)

On Error Resume Next
fso.DeleteFile filespec, forceDelete
End Sub

Using this method, our code would look like this:

FSODeleteFile fso, "C:\my files\*.docx"

DeleteFolder Method

The DeleteFolder method can delete folders as well as any files contained therein. There is no need to delete the files inside the folder first.

Sub TestFSO()

Dim fso As Object ' Scripting.FileSystemObject
Set fso = GetFSO
If fso Is Nothing Then
Exit Sub
End If
fso.DeleteFolder "C:\my files\"
End Sub

This procedure deletes the "C:\my files\" folder. Any files in that folder will also be deleted. I recommend encapsulating this function as follows:

Sub FSODeleteFolder(fso As Object, folderspec As String, Optional forceDelete As Boolean)

On Error Resume Next
fso.DeleteFolder folderspec, forceDelete
End Sub

A third parameter lets you specify if you want to delete read-only files. After creating the FSO object, we call the above code like this:

FSODeleteFolder fso, "C:\my files\"

We use On Error Resume Next because an error will occur if the folder doesn't exist. This is exactly what we want so we don't care if the method throws an error.

FolderExists Method

Used in conjunction with the CreateFolder method, the FolderExists method is used to determine if a given folder exists. This check is usually done before trying to create a folder.

Sub TestFSO()

Dim fso As Object ' Scripting.FileSystemObject
Set fso = GetFSO
If fso Is Nothing Then
Exit Sub
End If
Debug.Print fso.FolderExists "C:\my work files\"
End Sub

This method returns true if the folder exists, otherwise false. To encapsulate this, use the following function:

Function FSOFolderExists(fso As Object, folderspec As String) As Boolean

FSOFolderExists = (fso.FolderExists(folderspec))
End Function

You would then call the function like this, after instantiating the FSO Object:

FSOFolderExists fso, "C:\my files\"

GetDrive Method

This method returns a Drive object corresponding to the drive in a specified path. So you would declare a FSO Drive Object, then assign the return of this method to that object. Example:

Sub TestFSO()

Dim fso As Object ' Scripting.FileSystemObject
Dim drv As Object ' Scripting.Drive
Set fso = GetFSO
If fso Is Nothing Then
Exit Sub
End If
Set drv = fso.GetDrive(fso.GetAbsolutePathName("C:\my work files\"))
End Sub

Once you have a Drive Object, you can perform further operations, such as checking free disk space. To encapsulate this method, use the following function:

Function FSOGetDrive(fso As Object, folderspec As String) As String

FSOGetDrive = fso.GetDrive(fso.GetAbsolutePathName(folderspec))
End Function
Sub TestFSO()

Dim fso As Object ' Scripting.FileSystemObject
Dim drv As Object ' Scripting.Drive
Set fso = GetFSO
If fso Is Nothing Then
Exit Sub
End If
Set drv = FSOGetDrive(fso, "C:\my work files\")
End Sub

GetFile Method

This method returns a File Object corresponding to the file in a specified path. You would declare a FSO File Object then assign the result of this method to the File Object. Example:

Sub TestFSO()

Dim fso As Object ' Scripting.FileSystemObject
Dim file As Object ' Scripting.File
Set fso = GetFSO
If fso Is Nothing Then
Exit Sub
End If
Set file = fso.GetFile("C:\my work files\todays_spreadsheet.xlsm")
End Sub

We've encapsulated this method into the following function:

Function FSOGetFile(fso As Object, filespec As String) As Object ' Scripting.File

Set FSOGetFile = fso.GetFile(filespec)
End Function

Which you would then use like this:

Sub TestFSO()

Dim fso As Object ' Scripting.FileSystemObject
Dim file As Object ' Scripting.File
Set fso = GetFSO
If fso Is Nothing Then
Exit Sub
End If
Set file = FSOGetFile(fso, "C:\my work files\todays_spreadsheet.xlsm")
End Sub

GetFolder Method

Similar to the CreateFolder method, except the GetFolder method returns an existing folder. This method returns a Scripting.Folder Object.

fso.GetFolder "C:\my work files\"

If you need to do something further with the folder, return it to a Folder Object like this:

Sub TestFSO()

Dim fso As Object ' Scripting.FileSystemObject
Dim fldr As Object ' Scripting.Folder
Set fso = GetFSO
If fso Is Nothing Then
Exit Sub
End If
Set fldr = fso.GetFolder("C:\my work files\")
End Sub

You can use an encapsulated function to access this method:

Function FSOGetFolder(fso As Object, folderName As String) As Object ' Scripting.Folder

On Error Resume Next
Set FSOGetFolder = fso.GetFolder(folderName)
End Function
Sub TestFSO()
Dim fso As Object ' Scripting.FileSystemObject
Dim fldr As Object ' Scripting.Folder
Set fso = GetFSO
If fso Is Nothing Then
Exit Sub
End If
Set fldr = FSOGetFolder(fso, "C:\my work files\")
End Sub

GetSpecialFolder Method

This methods returns a Scripting.Folder Object representing one of three possible folders:

The Windows folder (0), the system folder (1), or the localtemp folder (2).

Sub TestFSO()

Dim fso As Object ' Scripting.FileSystemObject
Dim fldr As Object ' Scripting.Folder
Set fso = GetFSO
If fso Is Nothing Then
Exit Sub
End If
' get Windows folder
Set fldr = fso.GetSpecialFolder(0)
' or Set fldr = fso.GetSpecialFolder(WindowsFolder)
End Sub

You can use either the constant or its value, but if you use constants, you must declare them in your code.

Const WindowsFolder As Long = 0

Const SystemFolder As Long = 0
Const TemporaryFolder As Long = 0

An example encapsulation:

Function FSOGetSpecialFolder(fso As Object, folderSpec As Long) As Object ' Scripting.Folder

Select Case folderSpec
Case 0 To 2
Set FSOGetSpecialFolder = fso.GetSpecialFolder(folderSpec)
End Select
End Function