The VBA FileSystemObject (FSO) provides access to 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.
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")
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
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.