Analyst Cave

VBA FileSystemObject (FSO) in Excel – Methods and Properties

The VBA FileSystemObject (FSO) provides access the computer file system allowing you to create, delete, edit and copy files / folders. It allows you also to obtain various file and folder system properties. FileSystem Object is available in in all MS Office applications including for Excel VBA.

Do remember that the VBA FileSystemObject (FSO) is only supported in Windows systems. For Mac Os I recommend you use the built in native VBA methods for manipulating files and folders.

VBA FileSystemObject Methods

Below are listed functions extended by the VBA FileSystemObject (VBA FSO):

Create VBA FileSystemObject

The FSO object can be created using 2 separate approaches (similarly as most objects in VBA). The first is recommended as you don’t need to reference any libraries.

Late Binding

To create the FileSystemObject (FSO) in Excel VBA you can use the CreateObject function. The benefit is the lack of need of declaring the FSO object and hence necessity of referencing the library.

Dim fso as Object
Set fso = CreateObject("Scripting.FileSystemObject")

Early Binding

For learning it is better to reference the Microsoft.Scripting.Runtime library. Go to Tools->References and select Microsoft.Scripting.Runtime. This allows you to declare the FileSystemObject directly like this:

Dim fso as FileSystemObject
Set fso = New FileSystemObject

This approach is called Early Binding and requires referencing the library. I discourage this approach as it makes it harder to share the file with other users without having to explain how to reference the library.

Create Files and Folders

The VBA FSO object can be used to create either text files or to create folders in a directory:

To create a folder use the VBA CreateFolder method of the FSO object:

Set fso = CreateObject("Scripting.FileSystemObject")
'Create New Folder
fso.CreateFolder "C:\NewFolder"

Text files can be created using VBA CreateTextFile method:

Set fso = CreateObject("Scripting.FileSystemObject")
'Create the TextStream
Set ts = fso.CreateTextFile("C:\hello.txt")

The native VBA approach is better for creating binary files or writing to files.

Check Files, Folders, Drives exist

The VBA FSO object can be used to check if a File, Folder or Drive exists. Below are examples show how to check if one of these exists or is missing. You can also use the native VBA FileCopy procedure to copy files.

Set fso = CreateObject("Scripting.FileSystemObject")
 
Debug.Print fso.DriveExists("D") 'Result: True or False depending if Drive "D:" exists

Debug.Print fso.FileExists("D:\Hello.txt") 'Result: True or False depending if File exists

Debug.Print fso.FileExists("D:\SomeFolder") 'Result: True or False depending if Folder exists

Copy Files, Folders

The FileSystemObject allows you to copy files and folders. Lookup CopyFile and CopyFolder on details:

Set fso = CreateObject("Scripting.FileSystemObject")
 
'Copy just the Hello.xlsx file
fso.CopyFile "c:\Src\Hello.xlsx", "c:\Dest\" 

'Copy just the Hello folder
fso.CopyFolder "c:\Src\Hello", "c:\Dest\" 

Read and write to text files

The VBA FSO object allows you only to create text files. VBA natively allows you to create and write to files, I encourage you read my post on writing to files in VBA. The FSO object does not extend any methods for writing to binary files so you need to revert to the native VBA Open operator.

Below an example of how to create a text files using FSO, and writing a string into the file.

Dim fso as Object, ts as Object

Set fso = CreateObject("Scripting.FileSystemObject")

'Create the TextStream
Set ts = fso.CreateTextFile("C:\hello.txt")

'Write 2 lines ending with New Line character to text file
ts.WriteLine "Hello World!" 
ts.WriteLine "Hello People!"
'Close the file
ts.Close
Dim fso as Object, ts as Object

Set fso = CreateObject("Scripting.FileSystemObject")

'Open same file for reading
Set ts = fso.OpenTextFile("C:\hello.txt", ForReading, True, TristateFalse)
 
'Read till the end
Do Until ts.AtEndOfStream
     Debug.Print "Printing line " & ts.Line 
     Debug.Print ts.ReadLine 'Print a line from the file
Loop
ts.Close

Get File, Folder properties

The FSO object is less necessary for creating, reading/writing files or folders due to the fact that it replicates existing native VBA features. However, it comes useful when you want to obtain file or folder properties. Below a simple example of how to obtain a File and Folder various properties. For more details refer to the VBA GetFile and VBA GetFolder methods.

Set fso = CreateObject("Scripting.FileSystemObject")

'Example for a File
Set f = fso.GetFile("C:\Hello.txt") 'Return the File object
'Now we can obtain various properties of the File
Debug.Print f.DateCreated 'Date when file was created
Debug.Print f.Size 'Size of file in bytes


'Example for a Folder
 Set f = fso.GetFolder("C:\Src\") 'Return the Folderobject
 
'Now we can obtain various properties of the File
Debug.Print f.DateCreated 'Date when folder was created
Debug.Print f.Size 'Size of folder in bytes

You can also obtain similar properties for drives using the VBA GetDrive method.

Exit mobile version