FSO (File System Object) VBA

FSO (File System Object) VBA

FileSystemObject

File System Object in short FSO offers end to end solution on File Management, Directory Management and Drive Management for Windows. To access FileSystemObject user need reference Scrrun.dll. includes four other objects available for File I/O and other tasks. These objects include the File object, the TextStreamObject object, the Folder object, and the Drive object. All of these objects have properties and methods that are detailed in the Help files. We can access file system object using Early binding or Late binding method.

Binding

  1. Early binding: binding to the object reference happens during code writing.
  2. Late binding: compiler determines reference to the object at run time and performs binding.

Early binding

  • Navigate Tools menu and select Reference
  • Locate “Microsoft Scripting Runtime” as shown in figure 1.0
  • Click OK
figure 1.0

Code example

Public Sub EarlyBindingFSO()
    'Declare FSO object
    Dim FSO As FileSystemObject
    'Bind reference
    Set FSO = New FileSystemObject
End Sub

Late binding

Public Sub LateBindingFSO()
    'Declare FSO object
    Dim FSO As Object
    'Bind reference
    Set FSO = CreateObject("Scripting.FileSystemObject")
End Sub

VBA offers CreateObject (for more details please visit CreateObject vs GetObject VBA) which binds the reference of “Scripting.FileSystemObject” class into FSO object.

File System Members

Folders

Contains all folders under given path which can be taken using SubFolders property

Code example

Public Sub FolderFSOExample()
    'Declare FSO object
    Dim FSO As FileSystemObject
    'Bind reference
    Set FSO = New FileSystemObject
    
    'Declare folders collection
    Dim oFolders As Folders
    
    'Get folders collection
    Set oFolders = FSO.GetFolder("C:\Downloads\").SubFolders
    Debug.Print oFolders.Count
End Sub

Files

Contains all files under given directory which can be obtained using index or for each loop

Code example

Public Sub FileFSOExample()
    'Declare FSO object
    Dim FSO As FileSystemObject
    'Bind reference
    Set FSO = New FileSystemObject
    
    'Declare folders collection
    Dim oFolders As Folders
    
    'Get folders collection
    Set oFolders = FSO.GetFolder("C:\Users\nawazish\Downloads\").SubFolders
    
    'Iterate each folder
    Dim oFolder As Folder
    For Each oFolder In oFolders
        'Get all files under each folder
        Dim oFiles As Files
        Set oFiles = oFolder.Files
        'Print number of files in respective folder
        Debug.Print oFiles.Count
    Next oFolder
    
End Sub

Drives

Public Sub DriveFSOExample()
    'Declare FSO object
    Dim FSO As FileSystemObject
    'Bind reference
    Set FSO = New FileSystemObject
    
	'Declare variable to hold drives collection
    Dim oDrives As Drives
    Set oDrives = FSO.Drives
    
	'Iterate each drive and print drive letter i.e. C,D,E an so on
    Dim oDrive As Drive
    For Each oDrive In oDrives
        Debug.Print oDrive.DriveLetter
    Next oDrive
End Sub

BuildPath

Generates path from an existing path and a name

Public Sub BuildPathFSOExample()
    'Declare FSO object
    Dim FSO As FileSystemObject
    'Bind reference
    Set FSO = New FileSystemObject
    
    Const currntPath As String = "C:\users"
    Const newNameToBeAddedInPath As String = "myTestPath"
    
    Debug.Print FSO.BuildPath(currntPath, newNameToBeAddedInPath)    
End Sub

Output

C:\users\myTestPath

CopyFile

Copy a file to destination. It takes three arguments as:

  • Source: complete path of the file with file extension needs to be copied
  • Destination: path where file name needs to be copied
  • OverWriteFiles: its boolean property which take True/False, Overwrites file in given location if sets true

Code example

Public Sub CopyFileFSOExample()
    'Declare FSO object
    Dim FSO As FileSystemObject
    'Bind reference
    Set FSO = New FileSystemObject
    
	'Copy file
    FSO.CopyFile "C:\Users\1.jpg", "C:\Users\Downloads\", True
End Sub

CopyFolder

Copy a folder, it works similar to CopyFile method except it copies folder

Code example

Public Sub CopyFolderFSOExample()
    'Declare FSO object
    Dim FSO As FileSystemObject
    'Bind reference
    Set FSO = New FileSystemObject
    
	'Copy file
    FSO.CopyFolder "C:\Users\testFolder", "C:\Users\Downloads\", True
End Sub

CreateFolder

Public Sub CreateFolderFSOExample()
    'Declare FSO object
    Dim FSO As FileSystemObject
    'Bind reference
    Set FSO = New FileSystemObject
    
	Dim myFolder As Folder
    
	'Create a flder and hold reference
    Set myFolder = FSO.CreateFolder("C:\Users\Downloads\TestFolderNew")
    
End Sub

CreateTextFile

Takes followings parameters:

  • FileName: File name as string with path
  • Overwrite: boolean property, overwrites existing file if sets to true
  • Unicode: enables Unicode characters writing

Code example

Public Sub CreateTextFileFSOExample()
    'Declare FSO object
    Dim FSO As FileSystemObject
    'Bind reference
    Set FSO = New FileSystemObject
    
	'Crate text file
    Dim txtFile As TextStream
    Set txtFile = FSO.CreateTextFile("C:\Users\myTextfile.txt", True, False)
End Sub

DeleteFile

Public Sub DeleteFileFSOExample()
    'Declare FSO object
    Dim FSO As FileSystemObject
    'Bind reference
    Set FSO = New FileSystemObject
    
    'Declare folders collection
    Dim oFolders As Folders
    
    'Get folders collection
    Set oFolders = FSO.GetFolder("C:\Users\Downloads\").SubFolders
    
    'Iterate each folder
    Dim oFolder As Folder
    For Each oFolder In oFolders
    
		'Get all files under each folder
        Dim oFiles As Files
        Set oFiles = oFolder.Files
        
		'Declare file variable
        Dim oFile As File
        'Iterate each file
		For Each oFile In oFiles
            'Delete files starting with TRT name
            If Left(oFile.Name, 3) = "TRT" Then
                oFile.Delete
            End If
        Next oFile
    Next oFolder
    
End Sub

The above code will delete all file whose name starts with “TRT“

DeleteFolder

Public Sub DeleteFolderFSOExample()
    'Declare FSO object
    Dim FSO As FileSystemObject
    'Bind reference
    Set FSO = New FileSystemObject
    
    'Declare folders collection
    Dim oFolders As Folders
    
    'Get folders collection
    Set oFolders = FSO.GetFolder("C:\Users\Downloads\").SubFolders
    
    'Iterate each folder
    Dim oFolder As Folder
    For Each oFolder In oFolders
    
		'check folder name
		If oFolder.Name="download" Then
			'Delete folder
			oFolder.Delete
		End If
    Next oFolder
End Sub

DriveExists

Checks if drive or a share exists. Returns True if given drive name exists else False

Code example

Public Sub DriveExistsFSOExample()
    'Declare FSO object
    Dim FSO As FileSystemObject
    'Bind reference
    Set FSO = New FileSystemObject
	'Check drive exists
    Debug.Print FSO.DriveExists("C")
End Sub

FileExists

Checks if file exists. Returns True if file exists in given location

Code example

Public Sub FileExistsFSOExample()
    'Declare FSO object
    Dim FSO As FileSystemObject
    'Bind reference
    Set FSO = New FileSystemObject
	'Check file exists
    Debug.Print FSO.FileExists("C:\Users\Downloads\mytestFile.txt")
End Sub

FolderExists

Checks if given path exists. Returns True if folder exists in given location

Code example

Public Sub FolderExistsFSOExample()
    'Declare FSO object
    Dim FSO As FileSystemObject
    'Bind reference
    Set FSO = New FileSystemObject
	'Check folder exists
    Debug.Print FSO.FolderExists("C:\Users\Downloads")
End Sub

GetAbsolutePathName

Returns the canonical representation of the path

Code example

Public Sub AbsolutePathFSOExample()
    'Declare FSO object
    Dim FSO As FileSystemObject
    'Bind reference
    Set FSO = New FileSystemObject
	'Print Absolute Path Name
    Debug.Print FSO.GetAbsolutePathName("C:\Users\Downloads")
End Sub

GetBaseName

Returns base name from path. Below code will return myTest

Code example

Public Sub BaseNameFSO()
    'Declare FSO object
    Dim FSO As FileSystemObject
    'Bind reference
    Set FSO = New FileSystemObject
	'Print Base name
    Debug.Print FSO.GetBaseName("C:\Users\Downloads\myTest.txt")
End Sub

GetDrive

Gets drive or UNC share. Pass the drive letter and it returns valid Drive name. Below code will return “C:”

Code example

Public Sub GetDriveFSO()
    'Declare FSO object
    Dim FSO As FileSystemObject
    'Bind reference
    Set FSO = New FileSystemObject
	'get Drive
    Debug.Print FSO.GetDrive("c")
End Sub

GetDriveName

Returns drive from a path. Below code will return “C:”

Code example

Public Sub GetDriveNameFSO()
    'Declare FSO object
    Dim FSO As FileSystemObject
    'Bind reference
    Set FSO = New FileSystemObject
	'get Drive Name
    Debug.Print FSO.GetDriveName("C:\Users\Downloads\1.txt")
End Sub

GetExtensionName

Returns extension from path. Below code returns “txt”

Code example

Public Sub FileExtenstionFSO()
    'Declare FSO object
    Dim FSO As FileSystemObject
    'Bind reference
    Set FSO = New FileSystemObject
	'get Extension Name
    Debug.Print FSO.GetExtensionName("C:\Users\Downloads\1.txt")
End Sub

GetFile

Gets file. It helps to bind a file reference into an object from physical path as given below:

Code example

Public Sub GetFileExample()
    'Declare FSO object
    Dim FSO As FileSystemObject
    'Bind reference
    Set FSO = New FileSystemObject
	'Bind file reference
	Dim mFile As File
    Set mFile = FSO.GetFile("C:\Users\Downloads\myTest.txt")
End Sub

GetFileName

Returns the file name from a path. Below code will return “myTest.txt“

Code example

Public Sub GetFileNameFSOExample()
    'Declare FSO object
    Dim FSO As FileSystemObject
    'Bind reference
    Set FSO = New FileSystemObject
	
	'get File Name
    Set mFile = FSO.GetFileName("C:\Users\Downloads\myTest.txt")
End Sub

GetFileVersion

Retrieves the file version of the specified file into a string

Code example

Public Sub FileVersionFSO()
    'Declare FSO object
    Dim FSO As FileSystemObject
    'Bind reference
    Set FSO = New FileSystemObject
	
	'get File Version
    Set mFile = FSO.GetFileVersion("C:\Users\Downloads\myTest.txt")
End Sub

GetFolder

Gets folder. Binds the folder reference to an object variable from a path

Code example

Public Sub GetFoldersFSO()
    'Declare FSO object
    Dim FSO As FileSystemObject
    'Bind reference
    Set FSO = New FileSystemObject
	
	'get folder from path
    Dim mFolder As Folder
    Set mFolder = FSO.GetFolder("C:\Users\Downloads")
End Sub

GetParentFolderName

Returns path to the parent folder. Below example returns “Download” which is immediate parent in given path

Code example

Public Sub ParentFolderFSO()
    'Declare FSO object
    Dim FSO As FileSystemObject
    'Bind reference
    Set FSO = New FileSystemObject
	
	'get parent folder name
    Debug.Print FSO.GetParentFolderName("C:\Users\Downloads\myTest.txt")
End Sub

GetSpecialFolder

Get location of various system folders. Returns special folder path based on constant

  1. WindowsFolder: The Windows folder contains files installed by the Windows operating system. Refers to const value 0
  2. SystemFolder: The System folder contains libraries, fonts, and device drivers. Refers to const value 1
  3. TemporaryFolder: The Temp folder is used to store temporary files. Its path is found in the TMP environment variable. Refers to const 2

Code example

Public Sub SpecialFolderFSO()
    'Declare FSO object
    Dim FSO As FileSystemObject
    'Bind reference
    Set FSO = New FileSystemObject
	
	'get TEMP folder
    Debug.Print Debug.Print FSO.GetSpecialFolder(2)
End Sub

Output

C:\Users\vbaoverall\AppData\Local\temp

MoveFile

Public Sub MoveFileFSO()
    'Declare FSO object
    Dim FSO As FileSystemObject
    'Bind reference
    Set FSO = New FileSystemObject
	
	'move file
    FSO.MoveFile "C:\Users\Downloads\testFile.txt", "C:\Users\Downloads\NewFolder\testFile.txt"
End Sub

MoveFolder

Public Sub MoveFoldersFSO()
    'Declare FSO object
    Dim FSO As FileSystemObject
    'Bind reference
    Set FSO = New FileSystemObject
	
	'move folder
    FSO.MoveFolder "C:\Users\Downloads\test", "C:\Users\Downloads\TestFolderNew\"
End Sub

OpenTextFile

Opens a file as TextStream. This method take following arguments:

  • FileName: A valid file name with complete path
  • IOMode: Input or Output mode has following constants
    • ForAppending: if you need to open existing file and append text
    • ForReading: to read text from file
    • ForWriting: to write text into file
  • Create: its a boolean property which creates new file if doesn’t exists if sets to True
  • Format: refers to following Tristate constants as given below:
    • TristateFalse: Opens the file as ASCII.
    • TristateMixed : Opens the file as ASCII as well as Unicode
    • TristateTrue: Opens the file as Unicode.
    • TristateUseDefault: Opens the file by using the system default.

Next >> Get Files from sub directories or child folders VBA example

facebookShare on Facebook
TwitterTweet

Leave a Reply

Your email address will not be published. Required fields are marked *