How to Create, Write and Read text file in Excel using VBA FileSystemObject

I have previously shared an article in which I have explained how to use the VBA FileSystemObject to work with local files and folders from your Excel worksheet. Now, let’s see how using the methods in the FileSystemObject we can create a text file, write into the file and read its contents.

------------------

Here's a list of articles in this blog that explains how to use the FileSystemObject for various operations.

1) Quickly get or extract filenames from FilePaths in Excel using VBA Macro: Let’s assume, you have a list of filepaths in one of the columns in your excel sheet and you want extract only filenames from each given file path and write the name of the file in the next column.

2) How to copy or move files from one folder to another in Excel using VBA: The example in this articles explains how easily you can move files across various folders using the methods from VBAs FileSystemObject.

------------------

One the easiest way to write and read a text file in Excel is by using the TextStream object in VBA. This is how you will actually define a TextStream object.

Dim objTS As TextStream

Before defining it, you’ll have to reference Microsoft Scripting Library in your VBA application.

Once defined, you will have access to all the methods and properties in the TextStream object. However, you will have to initialize the object with the TextStream object returned by a FileSystemObject method. Like this,

Set objTS = objFso.CreateTextFile(sFolder & "/birds.json")

Let us see an example.

Open an Excel file and save the file in .xlsm format (it’s the macro format). Press Ctrl + F11 keys to open the VBA editor. From the Project Explorer, double click Sheet1 and write the code.

Option Explicit

Dim objFso As New FileSystemObject      ' Create FSO object.
Dim objTS As TextStream    ' For TextStream object.
Dim sFolder As String

Sub CreateFile()
    sFolder = "D:/birds"
    
    ' JSON data to write in the file.
    Dim sText As String
    sText = "[{ 'ID': '001', 'Name': 'Eurasian Collared-Dove',  'Type': 'Dove'}, "
    sText = sText & "{ 'ID': '002', 'Name': 'Bald Eagle', 'Type: Hawk'}, "
    sText = sText & "{ 'ID': '003', 'Name': 'Coopers Hawk', 'Type': Hawk'}]"
    
    ' Return a TextStream object using the FSO CreateTextFile() method.
    Set objTS = objFso.CreateTextFile(sFolder & "/birds.json")
    
    objTS.WriteLine sText       ' Write the text in the file.
    objTS.Close                 ' Close the file.
    
    ReadFile            ' Now, read contents of the file.
End Sub

Sub ReadFile()
    sFolder = "D:\birds"
    
    Set objTS = objFso.OpenTextFile(sFolder & "/birds.json")      ' Open the file.
    
    Do While Not objTS.AtEndOfStream           ' Loop though the file and read its contents.
        Debug.Print objTS.ReadLine
    Loop

    objTS.Close        ' Close the file.
End Sub

Note: Instead of a .txt file, I am creating a .json file for my example. The procedure is the same for a .txt file.

After referencing Microsoft Scripting Library, I have defined two objects in beginning.

Dim objFso As New FileSystemObject
Dim objTS As TextStream

There are two separate procedures for creating, writing and reading the text file. The text is a JSON array and I am creating JSON file named birds.json.

The CreateTextFile() method of FileSystemObject, returns a TextStream object. And, here I am initializing the TextStream object variable (I have declared in the beginning).

Set objTS = objFso.CreateTextFile(sFolder & "/birds.json")

The methods WriteLine (to write in the file) and Close are TextStream methods.

The second procedure opens and reads the JSON file (it can be a .txt file). Here again, I am using the OpenTextFile() method of the FileSystemObject to initialize the TextStream object. This time it’s in open mode, so it can read the contents.

Set objTS = objFso.OpenTextFile(sFolder & "/birds.json")

Run a simple Do While until it reaches the end the stream (or file). The “ReadLine” method will read each line in the file and write in the debug window. Along will AtEndOfStream property, you can use the AtEndOfLine property.

Do While Not objTS.AtEndOfLine
     Debug.Print objTS.ReadLine
Loop

Don’t forget to close the instance of the file, after performing an operation.

objTS.Close

← PreviousNext →