VBA FileSystemObject (FSO) in Excel – Methods and Properties

1 Star2 Stars3 Stars4 Stars5 Stars (No Ratings Yet)
Loading...

FileSystemObject - Methods and PropertiesThe 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.

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:

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.
VBA FileSystemObject Properties and methods

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:

Text files can be created using VBA CreateTextFile method:

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.

Copy Files, Folders

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

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.

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.

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

Simply the best place to learn Excel VBA