FileSystemObject (FSO) - VBA

What is FSO (FileSystemObject)

FileSystemObject (FSO) allows you to access the file system of your computer. Using it, you can access and modify the files/folders/directories in your computer system.

For example, below are some of the things you can do by using FileSystemObject in Excel VBA:

• Check if a file or a folder exists.
• Create or rename folders/files.
• Get a list of all the file names (or sub-folder names) in a folder.
• Copy files from one folder to another.
• Writing to files.
• Deleting files.
• Reading from files.

Enabling FileSystemObject in Excel VBA

by default FileSystemObject is not Enabled in Excel VBA, to enable the FSO we need to create reference to the FileSystemObject library.

Step 1: Go to the Tool menu

Step 2: Select the reference

Step 3 : Find the Microsoft Scripting Runtime library 

Step 4 : Click OK

Now our FSO library is enabled in the VBA, and we can use it to create FSO objects in our code

code that will set the object variable MyFSO as a FileSystemObject object:

Sub CreatingFSO() Dim MyFSO As FileSystemObject Set MyFSO = New FileSystemObject End Sub

Methods

Method	Description
BuildPath	Appends a name to an existing path.
CopyFile	Copies one or more files from one location to another.
CopyFolder	Copies one or more folders from one location to another.
CreateFolder	Creates a new folder.
CreateTextFile	Creates a text file and returns a TextStream object that can be used to read from, or write to the file.
DeleteFile	Deletes one or more specified files.
DeleteFolder	Deletes one or more specified folders.
DriveExists	Checks if a specified drive exists.
FileExists	Checks if a specified file exists.
FolderExists	Checks if a specified folder exists.
GetAbsolutePathName	Returns the complete path from the root of the drive for the specified path.
GetBaseName	Returns the base name of a specified file or folder.
GetDrive	Returns a Drive object corresponding to the drive in a specified path.
GetDriveName	Returns the drive name of a specified path.
GetExtensionName	Returns the file extension name for the last component in a specified path.
GetFile	Returns a File object for a specified path.
GetFileName	Returns the file name or folder name for the last component in a specified path.
GetFolder	Returns a Folder object for a specified path.
GetParentFolderName	Returns the name of the parent folder of the last component in a specified path.
GetSpecialFolder	Returns the path to some of Windows' special folders.
GetTempName	Returns a randomly generated temporary file or folder.
Move	Moves a specified file or folder from one location to another.
MoveFile	Moves one or more files from one location to another.
MoveFolder	Moves one or more folders from one location to another.
OpenAsTextStream	Opens a specified file and returns a TextStream object that can be used to read from, write to, or append to the file.
OpenTextFile	Opens a file and returns a TextStream object that can be used to access the file.
WriteLine	Writes a specified string and new-line character to a TextStream file.

Property

Property	Description
Drives	Returns a collection of all Drive objects on the computer.
Name	Sets or returns the name of a specified file or folder.
Path	Returns the path for a specified file, folder, or drive.
Size	For files, returns the size, in bytes, of the specified file; for folders, returns the size, in bytes, of all files and subfolders contained in the folder.
Type	Returns information about the type of a file or folder (for example, for files ending in .TXT, "Text Document" is returned).

Check if a file exist 

this code will check whether the file with the name ‘goku’ exists or not (in the specified location).

If the file  exists, the IF condition is True and it print – ‘The file Exists’ immediate window. And if it doesn’t exist, it shows print – The file Does Not Exist’

Sub CheckFolderExist() Dim MyFSO As FileSystemObject Set MyFSO = New FileSystemObject If MyFSO.FileExists("F:\goku.jpg") Then     debug.print "The file Exists" Else     debug.print "The file Does Not Exist" End If End Sub

Result

Create a New Folder 

this code will check whether the folder with the name ‘goku’ exists or not (in the specified location). If the folder exists, then the IF condition is True and it print – ‘The folder Exists’ on immediate window. And if it doesn’t exist, then it create the folder on the specific location

Sub CreateFolder() Dim MyFSO As FileSystemObject Set MyFSO = New FileSystemObject 'checks if folder exist or not     If MyFSO.FolderExists("C:\Users\ASR\Desktop\Testrun") Then     Debug.Print "The Folder Already Exist"    Else    'Create folder if not exist on the given location      MyFSO.CreateFolder ("C:\Users\ASR\Desktop\Testrun")    End If End Sub

Get all file names of given Folder 

this code will give us all the file name of the given folder. it will print all the file name from a specific location.

Sub GetFileNames() Dim MyFSO As FileSystemObject Dim MyFile As File Dim MyFolder As Folder Set MyFSO = New Scripting.FileSystemObject Set MyFolder = MyFSO.GetFolder("F:\vba learning")     For Each MyFile In MyFolder.Files         Debug.Print MyFile.Name     Next MyFile End Sub

Result

22.xlsx auditbook.xlsm Book1.xlsx excel dashboard.xlsx Excel-Exposure-Master-Workbook-7-26-2017.xlsx file and folder.xlsm inventroy pull.xlsm loops and error.xlsm Movies.accdb Movies.xlsm production report.xlsm