VBA FileSystemObject | Initial Setup | FSO Methods | Application Ideas | Final Thoughts

VBA FileSystemObject

Use the FileSystemObject (FSO) to access drives, folders and files with VBA. The FSO is part of Windows Script Host, which provides scripting abilities like batch files. FSO is arguably one of the most powerful APIs you can access with VBA.

This page introduces you to the VBA FileSystemObject, explains every function and method of the FSO, and guides you to helpful tutorials describing how to use many of these FSO methods.

The FSO is the preferred option if you need to access a computer’s file system, as it comes with a whole slew of useful functions no matter what task is at hand. Many file-related tasks could be accomplished by resorting to traditional VBA statements and functions, but this would require much more coding on your part. The price of increased readability and maintainability of your code is a slight performance hit, though. However, for normal uses, the difference in performance between traditional VBA functions and the FSO is negligible.

Another advantage of the FileSystemObject is that its functions have been thoroughly tested. When you deal with files and folders you don’t want to make mistakes, since they could have dire consequences. For instance, imagine having to explain to your boss that a program you wrote deleted a folder with important files by mistake because your code didn’t work as expected! In other words, when you’re dealing with critical data, files or folders, you should always incorporate safeguards into your code. We’ll address this more specifically in subsequent articles on the FSO.

Note, the FSO can only be used with Excel 2000 or later versions.

Initial setup - VBA FileSystemObject

The FSO is a top-level object in the Microsoft Scripting Runtime object library. Before you can work with the FileSystemObject in VBA, you must set a reference to its object library in the Visual Basic Editor. First, open Excel and press Alt+F11 to launch the VBA editor. Complete the following steps to add a reference to the FileSystemObject in VBA:

  1. Select Tools > References… in the top menu of the Visual Basic Editor.
  2. Scroll down and select “Microsoft Scripting Runtime” on the list.
  3. Tick the checkbox to the left and click OK.

VBA Microsoft Scripting Runtime reference
VBA Microsoft Scripting Runtime reference

Next, paste the code below into a standard code module.

Sub FSOSetup()
'(1) Gives you access to the VBA FileSystemObject.
'(2) Must add reference to Microsoft Scripting Runtime:
'      Tools > References > Microsoft Scripting Runtime
'(3) After binding, you can use FSO functions/methods like:
'      FSO.FileExists("C:\MyFiles\Test.xlsm")

    Dim FSO As Scripting.FileSystemObject
   
    Set FSO = New Scripting.FileSystemObject
    
End Sub

Make powerful macros with our free VBA Developer Kit

It’s easy to copy and paste a macro like this, but it’s harder make one on your own. To help you make macros like this, we built a free VBA Developer Kit and wrote the Big Book of Excel VBA Macros full of hundreds of pre-built macros to help you master file I/O, arrays, strings and more - grab your free copy below.

I'll take a free VBA Developer Kit

You are now fully set up to start working with the powerful FSO functions! We’ll add links to detailed tutorials about many of these functions during the coming weeks, so bookmark this page and check back often.

The FileSystemObject is just one of several ways to work with files in VBA. If you want to become a true file I/O expert, you should look at our comprehensive VBA File I/O Cheat Sheet filled with 50+ tips for working with files and over 30 file input/output macro examples.

Note, the New statement creates an early binding of the scripting object. This enables you to view the “members” or functions of the FSO at design time through the Visual Basic Editor’s built-in “Auto List Members” feature. Typing FSO. after early binding the scripting object is how you begin using the FileSystemObject functions in VBA.

VBA FileSytemObject Auto List Members
VBA FileSytemObject Auto List Members

TIP: If the “Auto List Members” feature doesn’t appear to work, make sure it is turned on. In the Visual Basic Editor, click Tools > Options…. On the Editor tab, place a checkmark next to “Auto List Members” and click OK.

FSO Methods - VBA FileSystemObject

The FileSystemObject makes your life a lot easier when you’re working with files, folders and drives. Many of the FSO methods are self-explanatory, but some do require a bit of explaining. Let’s start by grouping the FileSystemObject methods into categories and then we’ll give a quick rundown of the members of each category.

Throughout this tutorial, we may use the terms “methods” and “functions” interchangeably. The proper term when describing the features of FSO is “methods” but the term “functions” is easier to understand.

This is a complete list of FileSystemObject methods you can use with VBA. We told you FSO was powerful! Incorporating it into your VBA macros makes you just as powerful. Again, we’ll add links to detailed VBA tutorials explaining how to use many of these methods in the future.

File-related FSO functions

Function Description
BuildPath Generates a path from an existing path and a file or folder name. 
FSO.BuildPath("C:\MyFiles", "Test.xlsm") ' returns the string "C:\MyFiles\Test.xlsm" - File path

FSO.BuildPath("C:\MyFiles", "Excel files") ' returns the string "C:\MyFiles\Excel files" - Folder path
CopyFile For copying one or more files. Check out our fully detailed tutorial on the FSO CopyFile function for some really powerful macro examples for copying files.
CreateTextFile Creates a file as a TextStream. Note, if you're writing a program meant to be used by people in different countries, you should NOT use the FSO for writing to binary files! This is because the user's default ANSI code page may differ from yours. Some countries, such as Japan, uses a double-byte character set, whereas most Western countries, including the US, don't. Unless the character sets match, the bytes you're writing to the file will be interpreted incorrectly!
DeleteFile Deletes one or more files.
FileExists Checks if a file exists. 
If FSO.FileExists(SourceFilePath) Then 'file exists
GetExtensionName Returns the extension from a filename.
GetFile Returns a File object corresponding to the file in a specified path.
GetFileName Returns the filename of a file as a string. 
strFileName = FSO.GetFileName("C:\path\to\MyFile.txt") 'returns MyFile.txt
GetFileVersion Returns the file version of the specified file as a string.
MoveFile Moves a file from one folder to another.
OpenTextFile Returns a specified file as a TextStream object. Through the TextStream object you can read from or append to the original file.

Folder-related FSO functions

Method Description
CreateFolder Creates a new folder. 
FSO.CreateFolder ("C:\Users\Cory\Desktop\MyFolder")
This will only create one folder level at a time. You'll need a more sophisticated code to build a multi-level folder structure at once.
DeleteFolder Deletes one or more folders and their contents.
FolderExists Checks if a folder exists. 
If FSO.FolderExists(strNewDestPath) Then 'folder exists
GetFolder Returns the folder object related to the directory passed in the function parameter. You can pair the GetFolder method with other FSO methods to list all files in a folder and subfolders.
GetParentFolderName Returns the name of the parent folder of the last component in a specified path as a string.
GetSpecialFolder Returns the special folder object specified in the function parameter. Special folders include the Windows folder (files installed by the Windows operating system), the System folder (libraries, fonts, and device drivers) and the Temp folder (used for storing temporary files).
GetStandardStream Returns the standard input, output or error stream of a TextStream object.
GetTempName Returns a randomly generated temporary file or folder name as a string. This is particularly useful for avoiding name conflicts when you're storing temporary files.
MoveFolder Moves a folder and its content from one location to another.

Drive-related FSO functions

Method Description
DriveExists Returns a boolean representing whether the drive letter exists. 
bBool = FSO.DriveExists("C:")
GetAbsolutePathName Returns a complete and unambiguous path from the incomplete path passed in the function parameter. This is particularly useful if you know the file or folder you're referencing is in a particular place, but you don't know the drive name. For instance, passing "\Program Files" as the function parameter will return "C:\Program Files", unless the user's drive has been assigned another name, e.g. "D:".
GetBaseName Returns the name of the last path component, less any file extension. For instance, passing "C:\MyFiles\Test.xlsm" as the function parameter will return the string "Test".
GetDriveName Returns the drive name from a path as a string.
GetDrive Returns an instance of a Drive object corresponding to the specified drive passed in the parameter to the function. For example, 
With FSO
    Set objDrive = .GetDrive(.GetDriveName(.GetAbsolutePathName("/Program Files")))
End With
returns an instance of the drive the folder, "Program Files", is on. For removable drives, it's recommend the GetDrive argument be pair with the IsReady property, like 
With FSO
    Set objdrive = .GetDrive("z:")
    If objdrive.IsReady Then
        '...
    End If
End With

Application Ideas - VBA FileSystemObject

The FileSystemObject is your Swiss Army knife when you need to access a computer’s file system through VBA. The possible applications of it are practically limitless. Perhaps you want to automate the collection and consolidation of data contained in non-Excel files from several of your coworkers? No problem! With the FSO functions, you can iterate through folders, find the required files, read or append to them as TextStreams and piece together your report exactly as you want.

Look forward to our future tutorials where you’ll learn about specific applications of the FSO and how to incorporate the necessary safeguards into them!

This tutorial was an introduction to the VBA FileSystemObject. We introduced a lot of information about all the things you can do by connecting VBA with the FSO, but we didn’t give you examples of many of these features. This is a living tutorial, so you’ll want to check back often as we add links to detailed tutorials covering many of the FSO methods listed above.

I hope you enjoyed this tutorial. When you’re ready to take your VBA to the next level, subscribe using the form below.