Using the FileSytemObject

The FileSystemObject (FSO) provides a useful way to access the filing system on your computer and network drives.  To use the FSO you first need to create a reference to the Microsoft Scripting Runtime Library.

In the VBE click Tools | References.

Check the option Microsoft Scripting Runtime and click OK.

References Excel VBA

You can browse the scripting objects, methods and properties using the Object Browser.  Select Scripting from the All Libraries drop down.

Select the Scripting Library

The FileSystemObject contains lots of useful methods that you can use for drives, folders and files.

FileSystemObject Methods and Propertiess

To use the File System Object you need to create a new instance of it as shown in the code below.

Get Methods

Get methods allow you to retrieve information about a drive, folder or file.

Get Drive

Use GetDrive to specify which drive you want to examine.  With the drive specified you can retrieve information about it using the properties shown in the Object Browser below.

Drive Properties

In this example we use the FreeSpace property of the Drive object to calculate the available space on a drive.

The code above creates the message box shown below.

MsgBox

GetFolder

Use GetFolder to specify which folder you want to examine.  With the folder specified you can retrieve information about it using the properties shown in the Object Browser below.

Folder properties

In this example we use the Files property to allow us to count the number of files present in a folder.

The code above creates the message box shown below.

MsgBox

GetFile

Use GetFile to specify which file you want to examine.  With the file specified you can retrieve information about it using the properties shown in the Object Browser below.

File Properties & Methods

In this example we use the DateLastModified property of the file object.

The code above creates the message box shown below.

Other Useful Methods

CreateFolder Method

The CreateFolder method has a single parameter: Path

CopyFile Method

The CopyFile method has three parameters: Source, Designation and OverWriteFiles (defaults to TRUE)

Note that you have to state the file name in the Destination parameter.

DeleteFile Method

The DeleteFile method has two parameters FileSpec and Force  (delete if Read-only defaults to FALSE)

MoveFile Method

The MoveFile method has two parameters: Source and Destination.

FolderExists Method

The FolderExists method has a single parameter: FolderSpec

FileExists Method

The FileExists method has a single parameter: FileSpec

Practical Examples

Copy Files With a Specific File Type

In this example the Type property of the File object is used to determine whether a file gets copied. We only want to copy .xlsx files.  Contents of MyFolder shown below.

Files of Different File Types

Contents of ExcelFolder (once code executed) shown below.

Excel Files

Copy All Excel File Types

The previous example would only have copied .xlsx files.  What if we want to copy all Excel file types: xlsx, xls, and xlsm. Contents of MyFolder shown below.

Files of Different File Types

We can return the file type using the Scripting FileSystemObject method GetExtensionName

The first two characters of an Excel file extension are xl. Using the Left function we can determine if this is the case.

We can then loop this code for each file in our folder.

Contents of All Excel Files folder (once code executed) shown below.

Organise Files Based on File Type

In this example we create a folder for each file type of files found in MyFolder and then copy the files to the appropriate folder.

Contents of MyFolder shown below.

Files of Different File Types

Resulting folders shown below.

Files in Folders

Organise Files Based on File Name

In this example we want to organise files based on file name.  We want a folder for each customer: HYT and XYZ and within the customer folder we want a subfolder for the month that each file relates to.

Sub folders with the HYT customer folder.

Contents of the September sub folder.

Posted by Chester Tugwell