VBA File System

To work with the file system in VBA use VBA's built-in file-handling functions and statements as well as the Microsoft Scripting Runtime Library.

Built-In File Handling

VBA has built-in functions and statements for working with the file system. These functions and statements can be used to accomplish CRUD tasks relating to files and directories.

Statement/Procedure	Description
Open	Opens a file for input and/or output.
FreeFile	Returns the next available file number for use with the Open statement.
Width	Sets the output line width for an open file.
Close	Closes a file opened with the Open statement.
Reset	Closes all files opened with the Open statement.
Lock	Prevents access to all or part of a file by other processes.
Unlock	Allows access to all or part of a file by other processes.
Print	Writes display-formatted data to a sequential file.
Spc	Used with Print or Debug.Print to position output using spaces.
Tab	Used with Print or Debug.Print to position output using tabs.
Write	Writes data to a sequential file. Strings are quoted.
Put	Writes data from a variable to a file.
Get	Reads data from a file to a variable.
Line Input	Reads a line from an open sequential file into a String variable.
Input	Reads data from an open sequential file into variables.
Input$	Reads data as strings from an open sequential file into variables.
InputB	Reads data from a binary file into variables.
InputB$	Reads data as strings from a binary file into variables.
Name	Renames a file or directory.
FileCopy	Copies a file.
Kill	Deletes files.
EOF	Returns True when the end of a file opened for input has been reached.
LOF	Returns the size in bytes of an open file.
Loc	Returns the current read/write position within an open file.
Seek	Sets the position for the next read/write operation within an open file.
FileLen	Returns the length of a file in bytes.
FileDateTime	Returns the date and time when a file was created or last modified.
FileAttr	Returns the file mode of an open file.
SetAttr	Sets the attributes of a file.
GetAttr	Returns the attributes of a file or directory.
Dir	Returns the name of a file or directory that matches a given pattern, file attribute, or the volume label of a drive. If not found, returns an empty string.
Chdir	Changes the current directory.
Chdrive	Changes the current drive.
Curdir	Returns the current path as Variant/String.
Curdir$	Returns the current path as String.
Mkdir	Creates a directory.
Rmdir	Deletes a directory.

Microsoft Scripting Runtime Library

To accomplish more complex file system tasks in VBA, the Microsoft Scripting Runtime library can be used. The Scripting Runtime library contains classes which greatly enable working with the file system.

Classes

The Scripting Runtime library contains classes that represent parts of the file system. Drives can contain folders and folders can contain folders and files.

Option Explicit

Public Sub Example()

    Dim FSO     As Object 'Scripting.FileSystemObject
    Dim DColl   As Object 'Scripting.Drives
    Dim D       As Object 'Scripting.Drive
    Dim FdColl  As Object 'Scripting.Folders
    Dim Fd      As Object 'Scripting.Folder
    Dim FColl   As Object 'Scripting.Files
    Dim F       As Object 'Scripting.File

    Set FSO = CreateObject("Scripting.FileSystemObject")

    Set DColl = FSO.Drives

    Set D = FSO.GetDrive("C")

    Set FdColl = D.RootFolder.SubFolders

    Set Fd = FSO.GetFolder("C:\")

    Set FColl = FD.Files

    Set F = FSO.GetFile("C:\example.txt")

End Sub

FileSystemObject

The FileSystemObject class contains methods that make working with the file system simple and easy. The FileSystemObject class can be used to get information about the file system, manipulate paths, and to manipulate drives, folders, and files.

Functions	Description
BuildPath	Joins elements of a path with the path separator.
CopyFile	Copies one or more files. Supports wildcards.
CopyFolder	Copies one or more folders. Supports wildcards.
CreateFolder	Creates a folder.
CreateTextFile	Creates a text file and returns a TextStream object.
DeleteFile	Deletes one or more files. Supports wildcards.
DeleteFolder	Deletes one or more directories. Supports wildcards.
DriveExists	Checks if a drive exists.
FileExists	Checks if a file exists.
FolderExists	Checks if a folder exists.
GetAbsolutePathName	Gets the complete path for a given path.
GetBaseName	Gets the base name of a folder or file path without a file extension.
GetDrive	Returns a Drive object for a given path.
GetDriveName	Gets the drive name for a given path.
GetExtensionName	Gets the file extension for a given path.
GetFile	Returns a File object for a given path.
GetFileName	Gets the last folder name or file name in a path. Does not remove extension.
GetFileVersion	Returns the version number of a file.
GetFolder	Returns a Folder object for a given path.
GetParentFolderName	Gets the parent folder from a path string.
GetSpecialFolder	Gets path to Windows' special folders.
GetStandardStream	Returns a TextStream representing stdin, stdout, and stderr streams.
GetTempName	Returns a random name that can be used to create a temporary file or folder.
MoveFile	Moves one or more files. Supports wildcards.
MoveFolder	Moves one or more folders. Supports wildcards.
OpenTextFile	Opens a text file as a TextStream.