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:

Sub CreateFSO()
Set MyFSO = CreateObject("Scripting.FileSystemObject")
End Sub

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:

Sub TestFSO()
Dim MyFSO As New FileSystemObject
End Sub

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

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.

Sub CheckExistance()
Dim MyFSO As New FileSystemObject
MsgBox MyFSO.DriveExists("C:")
MsgBox MyFSO.FolderExists("C:\temp\")
MsgBox MyFSO.FileExists("C:\temp\testfile.txt")
End Sub

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.

GetAbsolutePathname

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

Syntax is:

GetAbsolutePathName (pathspec)

Sub AbsolutePath()
Dim MyFSO As New FileSystemObject, Pth As String
Pth = "c:..."
MsgBox MyFSO.GetAbsolutePathName(Pth)
End Sub

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.

GetBaseName

This returns the name of a specified file or folder.

Syntax is:

GetBaseName (path)

Sub BaseName()
Dim MyFSO As New FileSystemObject, Pth As String
Pth = "C:\temp\testfile.txt"
MsgBox MyFSO.GetBaseName(Pth)
End Sub

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.

GetDrive

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

Syntax is:

GetDrive (drivespec)

Sub DriveInfo()
Dim MyFSO As New FileSystemObject, Pth As String, Dr As Drive
Pth = "C:"
Set Dr = MyFSO.GetDrive(Pth)
MsgBox Dr.FreeSpace
End Sub

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.

GetDriveName

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

Syntax is:

GetDriveName (path)

Sub DriveName()
Dim MyFSO As New FileSystemObject, Pth As String
Pth = "C:\temp\testfile.txt"
MsgBox MyFSO.GetDriveName(Pth)
End Sub

This will return ‘C:’

 

GetExtensionName

This will return the file suffix in the path specified.

Syntax is:

GetExtensionName (path)

Sub ExtensionName()
Dim MyFSO As New FileSystemObject, Pth As String
Pth = "C:\temp\testfile.txt"
MsgBox MyFSO.GetExtensionName(Pth)
End Sub

This will return ‘txt’.

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

 

GetFile

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

Syntax is:

GetFile (filespec)

Sub FileInfo()
Dim MyFSO As New FileSystemObject, Pth As String, Fn As File
Pth = "C:\temp\testfile.txt"
Set Fn = MyFSO.GetFile(Pth)
MsgBox Fn.DateCreated
End Sub

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.

Sub FileName()
Dim MyFSO As New FileSystemObject, Pth As String
Pth = "C:\temp\testfile.txt"
MsgBox MyFSO.GetFileName(Pth)
End Sub

This will return ‘testfile.txt’.

 

GetFolder

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)

Sub FolderInfo()
Dim MyFSO As New FileSystemObject, Pth As String, Fo As Folder
Pth = "C:\temp"
Set Fo = MyFSO.GetFolder(Pth)
MsgBox Fo.DateCreated
End Sub

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:

Sub FileNames()
Dim MyFSO As New FileSystemObject, Pth As String, Fo As Folder, Fn As File
Pth = "C:\temp"
Set Fo = MyFSO.GetFolder(Pth)
For Each Fn In Fo.Files
    MsgBox Fn.Name
Next Fn
End Sub

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

 

GetParentFolderName

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

Syntax is:

GetParentFolderName (path)

Sub FolderName()
Dim MyFSO As New FileSystemObject, Pth As String, Fo As Folder
Pth = "C:\users\richard"
MsgBox MyFSO.GetParentFolderName(Pth)
End Sub

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.

CreateFolder

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)

Sub CreateNewFolder()
Dim MyFSO As New FileSystemObject, Pth As String
Pth = "C:\temp\MyFolder"
If MyFSO.FolderExists(Pth) = False Then
    MyFSO.CreateFolder (Pth)
End If
End Sub

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

CreateTextFile

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

Syntax is:

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

Sub CreateTextFile()
Dim MyFSO As New FileSystemObject, Pth As String
Pth = "C:\temp\Myfile.txt"
Set Fn = MyFSO.CreateTextFile(Pth,True)
Fn.Write "Add my own text here" & vbLf & "This is the second line"
Fn.Close
End Sub

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.

 

Using the ‘Copy’ Methods

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

CopyFile

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 source, destination, [ overwrite ]

Sub CopyFile()
Dim MyFSO As New FileSystemObject
MyFSO.CopyFile "C:\temp\*.txt", "C:\temp\myfolder\", True
End Sub

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.

CopyFolder

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

Syntax is:

CopyFolder source, destination, [ overwrite ]

Sub CopyFolder()
Dim MyFSO As New FileSystemObject
MyFSO.CopyFolder "C:\temp\*", "C:\users\richard\"
End Sub

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.

MoveFile

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 source, destination

Sub MoveAFile()
Dim MyFSO As New FileSystemObject
MyFSO.MoveFile "C:\temp\*", "C:\temp\myfolder"
End Sub

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.

MoveFolder

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

Syntax is:

MoveFolder (source, destination)

Sub MoveAFolder()
Dim MyFSO As New FileSystemObject
MyFSO.MoveFolder "C:\temp\myfolder", "C:\temp\mydestination"
End Sub

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.

DeleteFile

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

Syntax is:

DeleteFile filespec, [ force ]

Sub DeleteFiles()
Dim MyFSO As New FileSystemObject
MyFSO.DeleteFile "C:\temp\*"
End Sub

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.

DeleteFolder

This method deletes a specified folder and its contents.

Syntax is:

DeleteFolder folderspec, [ force ]

Sub DeleteFolders()
Dim MyFSO As New FileSystemObject
MyFSO.DeleteFolder "C:\temp\MyDestination"
End Sub

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

OpenAsTextStream.

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).

Sub TextStream()
Dim MyFSO As New FileSystemObject
Set f = MyFSO.GetFile("C:\temp\myfile.txt")
Set ts = f.OpenAsTextStream(2)
ts.Write "My new text"
ts.Close
Set ts = f.OpenAsTextStream(1)
s = ts.ReadLine
MsgBox s
ts.Close
End Sub

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.

BuildPath

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 (path, name)

Sub BuildPth()
Dim MyFSO As New FileSystemObject
np = MyFSO.BuildPath("C:\temp", "ANewFolder")
MsgBox np
End Sub

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

OpenTextFile

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.

Sub OpenTxtFile()
Dim MyFSO As New FileSystemObject
Set ts = MyFSO.OpenTextFile("C:\temp\myfile.txt", ForReading, False, TristateUseDefault)
s = ts.ReadLine
MsgBox s
ts.Close
End Sub

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

Drives

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

Sub Drv()
Dim MyFSO As New FileSystemObject, d As Drive
Set Dr = MyFSO.Drives
For Each d In Dr
    MsgBox d.DriveLetter
Next d
End Sub

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

Name

This returns the name of a specified file or folder.

Sub NameExample()
Dim MyFSO As New FileSystemObject
Set f = MyFSO.GetFile("C:\temp\myfile.txt")
i = f.Name & " on Drive " & UCase(f.Drive) & vbCrLf
i = i & "Created: " & f.DateCreated & vbCrLf
i = i & "Last Accessed: " & f.DateLastAccessed & vbCrLf
i = i & "Last Modified: " & f.DateLastModified
MsgBox i
End Sub

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

Path

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

Sub PathExample()
Dim MyFSO As New FileSystemObject
Set f = MyFSO.GetFile("C:\temp\myfile.txt")
i = f.Path & f.Name & " on Drive " & UCase(f.Drive) & vbCrLf
i = i & "Created: " & f.DateCreated & vbCrLf
i = i & "Last Accessed: " & f.DateLastAccessed & vbCrLf
i = i & "Last Modified: " & f.DateLastModified
MsgBox i
End Sub

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

Size

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

Sub FSize()
Dim MyFSO As New FileSystemObject
Set f = MyFSO.GetFolder("C:\temp\")
MsgBox f.Size
End Sub

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

Sub FSize()
Dim MyFSO As New FileSystemObject
Set f = MyFSO.GetFile("C:\temp\myfile.txt")
MsgBox f.Size
End Sub

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

Type

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

Sub FType()
Dim MyFSO As New FileSystemObject
Set f = MyFSO.GetFolder("C:\temp\")
MsgBox f.Type
End Sub

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

Sub FType()
Dim MyFSO As New FileSystemObject
Set f = MyFSO.GetFile("C:\temp\myfile.txt")
MsgBox f.Type
End Sub

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

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