5 Ways To Master File System Object In Vba

Mastering the File System Object (FSO) in VBA is a crucial skill for any Excel developer or power user. The FSO provides a powerful tool for interacting with the file system, allowing you to create, delete, and manipulate files and folders with ease. In this article, we will explore five ways to master the File System Object in VBA, covering topics such as creating and deleting files and folders, reading and writing to text files, and working with file properties.

1. Creating and Deleting Files and Folders

One of the most basic yet powerful functions of the FSO is creating and deleting files and folders. This can be achieved using the following code:

Sub CreateFolder()
    Dim fso As Object
    Set fso = CreateObject("Scripting.FileSystemObject")
    fso.CreateFolder "C:\NewFolder"
    Set fso = Nothing
End Sub

Sub DeleteFolder()
    Dim fso As Object
    Set fso = CreateObject("Scripting.FileSystemObject")
    fso.DeleteFolder "C:\NewFolder"
    Set fso = Nothing
End Sub

In the above code, we first create a new instance of the FSO using the CreateObject method. We then use the CreateFolder method to create a new folder at the specified path. Similarly, we use the DeleteFolder method to delete a folder.

2. Reading and Writing to Text Files

Another essential function of the FSO is reading and writing to text files. This can be achieved using the following code:

Sub WriteToFile()
    Dim fso As Object
    Set fso = CreateObject("Scripting.FileSystemObject")
    Dim ts As Object
    Set ts = fso.CreateTextFile("C:\NewFile.txt", True)
    ts.WriteLine "Hello World!"
    ts.Close
    Set ts = Nothing
    Set fso = Nothing
End Sub

Sub ReadFromFile()
    Dim fso As Object
    Set fso = CreateObject("Scripting.FileSystemObject")
    Dim ts As Object
    Set ts = fso.OpenTextFile("C:\NewFile.txt", 1)
    MsgBox ts.ReadLine
    ts.Close
    Set ts = Nothing
    Set fso = Nothing
End Sub

In the above code, we first create a new instance of the FSO using the CreateObject method. We then use the CreateTextFile method to create a new text file at the specified path. We then write to the file using the WriteLine method. Similarly, we use the OpenTextFile method to open an existing text file and read from it using the ReadLine method.

3. Working with File Properties

The FSO also allows you to work with file properties such as file size, creation date, and modification date. This can be achieved using the following code:

Sub GetFileSize()
    Dim fso As Object
    Set fso = CreateObject("Scripting.FileSystemObject")
    Dim file As Object
    Set file = fso.GetFile("C:\NewFile.txt")
    MsgBox file.Size
    Set file = Nothing
    Set fso = Nothing
End Sub

Sub GetCreationDate()
    Dim fso As Object
    Set fso = CreateObject("Scripting.FileSystemObject")
    Dim file As Object
    Set file = fso.GetFile("C:\NewFile.txt")
    MsgBox file.DateCreated
    Set file = Nothing
    Set fso = Nothing
End Sub

In the above code, we first create a new instance of the FSO using the CreateObject method. We then use the GetFile method to get a file object for the specified file. We then use the Size property to get the file size and the DateCreated property to get the creation date.

4. Working with Folder Properties

The FSO also allows you to work with folder properties such as folder size, creation date, and modification date. This can be achieved using the following code:

Sub GetFolderSize()
    Dim fso As Object
    Set fso = CreateObject("Scripting.FileSystemObject")
    Dim folder As Object
    Set folder = fso.GetFolder("C:\NewFolder")
    MsgBox folder.Size
    Set folder = Nothing
    Set fso = Nothing
End Sub

Sub GetFolderCreationDate()
    Dim fso As Object
    Set fso = CreateObject("Scripting.FileSystemObject")
    Dim folder As Object
    Set folder = fso.GetFolder("C:\NewFolder")
    MsgBox folder.DateCreated
    Set folder = Nothing
    Set fso = Nothing
End Sub

In the above code, we first create a new instance of the FSO using the CreateObject method. We then use the GetFolder method to get a folder object for the specified folder. We then use the Size property to get the folder size and the DateCreated property to get the creation date.

5. Using the FSO with Loops

The FSO can also be used with loops to perform actions on multiple files and folders. This can be achieved using the following code:

Sub LoopThroughFiles()
    Dim fso As Object
    Set fso = CreateObject("Scripting.FileSystemObject")
    Dim folder As Object
    Set folder = fso.GetFolder("C:\NewFolder")
    Dim file As Object
    For Each file In folder.Files
        MsgBox file.Name
    Next file
    Set file = Nothing
    Set folder = Nothing
    Set fso = Nothing
End Sub

Sub LoopThroughFolders()
    Dim fso As Object
    Set fso = CreateObject("Scripting.FileSystemObject")
    Dim folder As Object
    Set folder = fso.GetFolder("C:\NewFolder")
    Dim subfolder As Object
    For Each subfolder In folder.Subfolders
        MsgBox subfolder.Name
    Next subfolder
    Set subfolder = Nothing
    Set folder = Nothing
    Set fso = Nothing
End Sub

In the above code, we first create a new instance of the FSO using the CreateObject method. We then use the GetFolder method to get a folder object for the specified folder. We then use a For Each loop to iterate through the files and subfolders in the folder.

Mastering the File System Object in VBA Image Gallery

We hope this article has helped you master the File System Object in VBA. With these five ways, you can create, delete, and manipulate files and folders with ease. Remember to practice and experiment with different code examples to become proficient in using the FSO. Happy coding!