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:
- Select Tools > References… in the top menu of the Visual Basic Editor.
- Scroll down and select “Microsoft Scripting Runtime” on the list.
- Tick the checkbox to the left and click OK.
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
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.
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
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.
Write better macros in half the time
I see people struggling with Excel every day and I want to help. That's why I'm giving away my personal macro library for free. This powerful gift lets you automatically import all my macros directly into your spreadsheets with just one click.
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
|BuildPath||Generates a path from an existing path and a file or folder name.
|CopyFile||For copying one or more 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.|
|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.|
|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.|
|DeleteFolder||Deletes one or more folders and their contents.|
|FolderExists||Checks if a folder exists.|
|GetFolder||Returns the folder object related to the directory passed in the function parameter.|
|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||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
returns an instance of the drive the folder, "Program Files", is on.
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.
If you haven’t already done so, please subscribe to my free wellsrPRO VBA Training Program. What are you waiting for? You’ll love the great VBA content I send your way!This article was written by Michael H. Sorensen on behalf of The VBA Tutorials Blog.
About The VBA Tutorials Blog
The VBA Tutorials Blog was created by Ryan Wells, a Nuclear Engineer and professional VBA Developer. He created this site to help others learn to write macros in Excel. That's why he developed a unique 3-part free Excel training program to help others quickly learn VBA in a natural setting: right inside Excel.Follow