Return to VBA Code Examples

Using the FileSystemObject in Excel VBA

Using the FileSystemObject (FSO) in Excel VBA

The FileSystemObject (FSO) gives you access to a whole range of functions for accessing your computer’s file system. Using this object, you can easily access files, folders, and drives, and also read and write to files.

Many of the FSO functions could be written by you in traditional VBA, but would require more coding, and would be more difficult for an incoming developer to maintain and understand.  The FSO is a tried and tested API (Application Programming Interface) and is more reliable than your own code. It is easy to use and ready and available.

The FSO works to international standards and settings that you have on your computer.  If you are distributing your Excel application globally then using the FSO will take care of any differences in settings between countries, which your own code would have trouble doing.

The FSO will allow you to do almost everything in VBA code that you could do in Windows File Explorer. It gives you complete access to the Windows file system.

Creating a FileSystemObject

The FileSytemObject is not part of Excel VBA.  You can use the FSO by creating an object (late binding) in VBA:

Alternatively, you can add a reference into VBA for the FSO library.  This is called early binding and it is faster than late binding, since the object does not have to be created when your code is run.

To add a reference, you need to press Alt-F11 to enter the Visual Basic Editor (VBE), and then use ‘Tools|References’ from the VBE menu.  This will display a pop-up window for you to select the relevant reference (see below).

Scroll down the list of available references until you can see ‘Microsoft Scripting Runtime’.  Tick the box and click on OK, and the library is now part of your application.

The location of the DLL library file is C:\Windows\SysWOW64\scrrun.dll

If you are distributing your application to other colleagues or locations, it is essential that they have this file in the correct location on their computer, otherwise your code will error.

It is worth putting an error trap on the ‘WorkbookOpen’ event using the Dir command to check that the file exists.  If it is absent, then give a warning message and close the Excel file.

Once the reference has been added, you can use the following code to create the FSO:

All the examples in this article will use this methodology to create the FSO.

VBA 14 PIC 01

The FSO has many methods and properties available.  These are divided here into sections according to what they can do.

Using the ‘Exists’ Methods

You can use an FSO method to check whether a drive, a folder, or a file exists. These methods are easy to use and only require one parameter.

These statements will all return ‘True’ assuming that your computer has a C: drive, a folder on it called ‘Temp’ and a file in the Temp folder called ‘testfile.txt’

The text strings in the parameters are not case-sensitive.  You cannot use wildcards in any of these methods.

You also cannot use URLs (Uniform Resource Locators) to describe a folder or file location.  The FSO works purely on the Windows Operating System and the file system thereon.  For an external server location, you need to map a drive to this first of all, and then use the drive path itself.


Using the ‘Get’ Methods

The FSO has numerous methods to get information on the file and path, either splitting the path and file, or getting file or folder information such as date created or date modified.


This will provide a complete path from the root of the specified drive.

Syntax is:

GetAbsolutePathName (pathspec)

This will return a string ‘C:\Users\Richard\Documents’. This is because the path has been specified as C: followed by three dots.  Each dot signifies a next level within the folder structure.


This returns the name of a specified file or folder.

Syntax is:

GetBaseName (path)

This code will return ‘testfile’.  The method returns the last section in the path name. If it is a file, then it does not return the file suffix.

If the path cannot be found then a blank string will be returned.


This allows you to use code to access drive information, based on the drive letter specified.

Syntax is:

GetDrive (drivespec)

This method returns a drive object based on the drive specified. You can use this object to access information about the drive, such as free space available.

VBA Coding Made Easy

Stop searching for VBA code online. Learn more about AutoMacro - A VBA Code Builder that allows beginners to code procedures from scratch with minimal coding knowledge and with many time-saving features for all users! automacro

Learn More!!


This method will separate out the drive name from a path / filename string.

Syntax is:

GetDriveName (path)

This will return ‘C:’



This will return the file suffix in the path specified.

Syntax is:

GetExtensionName (path)

This will return ‘txt’.

If no file is specified, then an empty string will be returned.



This method returns a file object, which holds various information about the file itself.

Syntax is:

GetFile (filespec)

This will return the date and time that the specified file was created.  If no file is specified or the file does not exist, you will get a ‘file not found’ error.

This will return ‘testfile.txt’.


VBA Programming | Code Generator does work for you!


This creates a folder object for the base folder in the specified path. The path must only contain folder names.  No filenames must be included otherwise an error will occur.

Syntax is:

GetFolder (folderspec)

The folder object has various information in it which can be accessed. In this case, it returns the date that the folder was created.

You can also use this method to retrieve all the file names within a given folder:

This code will iterate through the ‘Temp’ folder and display each file name found.



This method will return the folder name in the next level up in the folder hierarchy.

Syntax is:

GetParentFolderName (path)

This will return ‘Users’ as this is the ‘parent’ for the folder ‘richard’.


Using the ‘Create’ Methods

With the FSO you can create a new folder and path and create a text file.


You can specify a new folder path name to be created. A danger of this is that if the folder already exists, then an error will occur.  You can use the method ‘FolderExists’ to ensure that this will not happen.

Syntax is:

CreateFolder (foldername)

This code will create a new folder called ‘MyFolder’ under the existing path ‘C:\temp’.


This method enables you to create a simple text file and to write directly into it.

Syntax is:

CreateTextFile (filename, [ overwrite, [ unicode ]])

This code creates a text file called ‘Myfile.txt’ in the ‘Temp’ folder of the ‘C:’ drive and then writes two lines of text to it.

Note that a line feed character is concatenated into the string being written.

If the path that you are writing to does not exist then an error will occur.  You can use the ‘FolderExists’ method to check this before creating the file.

There is an optional parameter to overwrite the existing file if required – this can be True or False.  The default is True.


AutoMacro | Ultimate VBA Add-in | Click for Free Trial!

Using the ‘Copy’ Methods

You can use these methods to copy a file or a folder to another location.


This method will copy a file from one folder location to another.  Note that the copy will fail if the destination location has the read-only attribute set.

Syntax is:

CopyFile sourcedestination, [ overwrite ]

This code will make a copy of all the text (txt) files at ‘C:\temp’ into ‘C:\temp\myfolder\’, overwriting the file where necessary. The default setting for Overwrite is True.

You can use an asterisk (*) wildcard for the filenames, but you cannot use a question mark (?) wildcard to represent single characters.


You can use this method to copy an entire folder from one location to another.

Syntax is:

CopyFolder sourcedestination, [ overwrite ]

This code copies all the folders and files below ‘C:\temp’ into ‘C:\users\richard’. The new folder created will be ‘C:\users\richard\myfolder’ as ‘C:\temp’ had a folder within it called ‘myfolder’.

There are four possible outcomes when using this method:

  • If the destination does not exist, then the source folder and contents is copied.
  • If the destination already exists, then an error occurs.
  • If the destination is a folder, then the source folder and its contents will be copied. An error will occur if Overwrite is set to False and there is already a copy of a file in the destination.
  • If the destination is set to read only, an error will occur if overwrite is set to false.

This method stops at the first error that it encounters.  There is no rollback of any actions that have succeeded before the error occurs.

Using the ‘Move’ Methods

These methods can be used to move files or folders to other locations. This is the same as cutting from one location and pasting into another location. Note that if the file to be moved is open, then the Move method will fail with an error.

AutoMacro | Ultimate VBA Add-in | Click for Free Trial!


This method is used to move a specific file to another location.  Wildcards are allowed in the last path component of the source.

Syntax is:

MoveFile sourcedestination

This code moves all the files found at ‘C:\temp’ into ‘C:\temp\myfolder’.

The source and destination folders must exist, as the destination folder does not automatically get created.

This method stops at the first error that it encounters.  There is no rollback of any actions that have succeeded before the error occurs.


This method moves a specific folder from one location to another.

Syntax is:

MoveFolder (sourcedestination)

This code moves the folder ‘myfolder’ and contents to the folder ‘mydestination’. ‘myfolder’ is effectively deleted and ‘mydestination’ is created, together with the contents from ‘myfolder’.

If the destination folder already exists then an error occurs.


Using the ‘Delete’ Methods

These methods are used to delete files or folders. They must be used with care as there is no rollback or undo methods if anything goes wrong.


This deletes individual files or a group of files using wildcards.

Syntax is:

DeleteFile filespec, [ force ]

This code will delete all the files in the folder ‘C:\temp’

The Force parameter is optional and is set to True or False. If it is set to True, then read-only files will be deleted.  The default is False.

AutoMacro | Ultimate VBA Add-in | Click for Free Trial!


This method deletes a specified folder and its contents.

Syntax is:

DeleteFolder folderspec, [ force ]

This code will delete the folder ‘MyDestination’ and all the files within that folder. The folder ‘temp’ will remain.

The Force parameter is optional and is set to True or False. If it is set to True, then read-only folders will be deleted.  The default is False.

Wildcards can be used in the last component of the path. If the folder is not found then an error will occur.

This method stops at the first error that it encounters.  There is no rollback of any actions that have succeeded before the error occurs.


Other Methods in the FSO


This method opens a specified file as a Text Stream object and allows it to be read or written to. The advantage of this method is that it can open any file type and extract the available text.

Syntax is:

OpenAsTextStream ([ iomode, [ format ]])

The ‘iomode’ parameter allows read only (1), read/write (2), and appending (8).  The read/write parameter overwrites the file.

The ‘format’ parameter is set to -2 for system default, -1 to open the file as Unicode, and 0 to open the file as ASCII (American Standard Code for Information Interchange).

This code gets an existing text file and creates it as an object using the ‘GetFile’ method.  It then opens the text stream as read / write (2) and writes a line of text. The file is then closed and re-opened as read (1) and a line is read from it, which is then displayed as a message box.

Note that the read line must be placed in a variable before it can be displayed in a message box.


This method will append a folder or file name to the end of an existing folder path. This only creates a text string and does not actually create the new folder.

Syntax is:

BuildPath (pathname)

This will display ‘C:\temp\ANewFolder’. However, if you want to actually use this folder, you need to use the ‘CreateFolder’ method.

AutoMacro | Ultimate VBA Add-in | Click for Free Trial!


This method allows files to be opened and read from or written to according to set parameters. It works in a similar way to the OpenAsTextStream method.

Syntax is:

OpenTextFile (filename, [ iomode, [ create, [ format ]]])


The ‘iomode’ parameter allows ForReading, ForWriting, and ForAppending.  The ForWriting parameter overwrites the file.

The ‘create’ parameter is a Boolean value. True means that a new file will be created if the specified filename does not exist. False means that no file will be created if the filename is not found.  The default is False.

The ‘format’ parameter can be set to TristateFalse, TristateMixed, TristateTrue, and TristateUseDefault depending on whether the file is ASCII or Unicode.

This code will read a line from the text file ‘myfile.txt’.

An advantage which the OpenTextFile method has over the OpenAsTextStreamMethod is that it has drop downs for the parameters, which are more meaningful than trying to remember the appropriate numeric values for the various parameter options.


Properties of the FSO


This property holds a collection of available drives on your computer.

This code will return each drive letter available on your computer.


This returns the name of a specified file or folder.

This code will give the name of the file and information about it using the Drive property.

AutoMacro | Ultimate VBA Add-in | Click for Free Trial!


The Path property will separate the path out from a file specification.

This example works in the same way as the Name example, except that it now provides the path for the file.


The Size property will give the size of a folder or a file.

This code above will return the size of the folder ‘C:\temp\’.

This code above will return the size of the file ‘myfile.txt’.


The type property will return the text for the file or folder type.

This code above will return the text ‘File folder’.

This code above will return the text ‘Text document’.

Note the use of ‘GetFolder’ and ‘GetFile’ in each example.