9.2 FileSystemObject: Creating, Reading, Writing Files.

The FileSystemObject (FSO) is a powerful feature in VBA that allows you to interact with the file system. It provides a more sophisticated and flexible approach than using the basic Open, Write, and Input statements. Using the FSO, you can create, delete, read, write, and manage files and directories with ease. It simplifies file operations by offering object-oriented methods.

1. What is FileSystemObject (FSO)?

The FileSystemObject (FSO) is a part of the Microsoft Scripting Runtime library. It allows you to perform operations like:

• Creating and deleting files and folders
• Reading from and writing to files
• Checking file or folder attributes
• Copying or moving files
• Renaming files or folders

To use FSO in VBA, you need to reference the Microsoft Scripting Runtime library, which can be done as follows:

1. Open the VBA editor (press Alt + F11).
2. Go to Tools > References.
3. Scroll down and check Microsoft Scripting Runtime.
4. Click OK to enable FSO functionality.

2. Creating the FileSystemObject

You create an instance of the FileSystemObject using the CreateObject method or by declaring it with the New keyword.

Example: Creating a FileSystemObject

Dim fso As Object
Set fso = CreateObject("Scripting.FileSystemObject")

Alternatively, if you added the Microsoft Scripting Runtime reference:

Dim fso As FileSystemObject
Set fso = New FileSystemObject

3. Creating Files with FileSystemObject

To create a new file, use the CreateTextFile or CreateTextFile method. The CreateTextFile method creates a new text file and allows you to write content to it.

Example: Creating a New Text File

Dim fso As FileSystemObject
Dim txtFile As TextStream

Set fso = New FileSystemObject
Set txtFile = fso.CreateTextFile("C:\path\to\newfile.txt", True) ' True for overwriting if exists

txtFile.WriteLine "Hello, world!" ' Write a line to the file
txtFile.WriteLine "This is a new text file created with FileSystemObject."

txtFile.Close ' Always close the file after operations

In this example:

• CreateTextFile creates a text file at the specified path. The True argument specifies that the file should be overwritten if it already exists.
• WriteLine writes text to the file, and Close saves the changes.

4. Reading Files with FileSystemObject

To read from a file, you use the OpenTextFile method. This method allows you to open an existing text file and read its contents.

Example: Reading a Text File

Dim fso As FileSystemObject
Dim txtFile As TextStream
Dim fileContent As String

Set fso = New FileSystemObject
Set txtFile = fso.OpenTextFile("C:\path\to\existingfile.txt", 1) ' 1 for reading

fileContent = txtFile.ReadAll ' Read the entire content of the file
Debug.Print fileContent ' Display the content in the Immediate window

txtFile.Close ' Close the file after reading

In this example:

• OpenTextFile("C:\path\to\existingfile.txt", 1) opens the file for reading (the 1 indicates reading mode).
• ReadAll reads the entire content of the file into a variable.
• After reading, the file is closed using txtFile.Close.

You can also use other methods like ReadLine to read the file line by line, or Read to read a specified number of characters.

5. Writing to Files with FileSystemObject

In addition to writing a new file, you can append content to an existing file using the AppendTextFile method.

Example: Appending to an Existing Text File

Dim fso As FileSystemObject
Dim txtFile As TextStream

Set fso = New FileSystemObject
Set txtFile = fso.OpenTextFile("C:\path\to\existingfile.txt", 8, True) ' 8 for appending

txtFile.WriteLine "This is a new line added to the existing file."

txtFile.Close

In this example:

• The OpenTextFile method is used with 8 for appending. This mode adds data to the end of the file without overwriting its content.
• After appending the new line, the file is closed.

6. Checking File Existence

Before performing operations like reading or writing, you might want to check if a file exists. You can use the FileExists method.

Example: Checking if a File Exists

Dim fso As FileSystemObject
Set fso = New FileSystemObject

If fso.FileExists("C:\path\to\existingfile.txt") Then
    MsgBox "File exists!"
Else
    MsgBox "File does not exist!"
End If

In this example:

• The FileExists method checks whether the specified file exists and returns True or False accordingly.

7. Deleting Files

You can delete a file using the DeleteFile method of the FileSystemObject.

Example: Deleting a File

Dim fso As FileSystemObject
Set fso = New FileSystemObject

If fso.FileExists("C:\path\to\filetodelete.txt") Then
    fso.DeleteFile "C:\path\to\filetodelete.txt"
    MsgBox "File deleted successfully."
Else
    MsgBox "File not found!"
End If

In this example:

• The DeleteFile method deletes the file if it exists. You can also add the Force parameter to delete read-only files.

8. Summary of FileSystemObject Methods

• Creating Files: Use CreateTextFile to create new files and write to them.
• Reading Files: Use OpenTextFile to open files and read data using methods like ReadAll or ReadLine.
• Writing to Files: Use WriteLine, Write for writing or AppendTextFile for appending data to an existing file.
• Checking File Existence: Use FileExists to verify if a file exists before performing actions.
• Deleting Files: Use DeleteFile to remove files from the system.

9. Advanced File Operations

Beyond simple file creation, reading, and writing, the FileSystemObject provides methods for more complex file operations such as:

• Renaming files: Use the Name method.
• Copying and Moving files: Use CopyFile and MoveFile.
• Getting file attributes: Use GetFile to access file attributes like size, date created, and more.

By leveraging the FileSystemObject, you can easily automate file handling tasks in VBA, creating powerful file management routines to enhance your Excel or Access applications.