VBA – Write to Text File

This tutorial will demonstrate how to write to text files using VBA.

Write to a Text File

The below codes use the FileSystemObject (learn more). In order to use it, you will need to set a reference to the VB script run-time library.

Write to New Text File

With the CreateTextFile method of FileSystemObject you can create and then add content to a text file:

Sub FSOCreateAndWriteToTextFile() 
    Dim FSO As New FileSystemObject
    Set FSO = CreateObject("Scripting.FileSystemObject")
    Set FileToCreate = FSO.CreateTextFile("C:\Test\TestFile.txt")

    FileToCreate.Write "test line"
    FileToCreate.Close

End Sub

Please note that content will not be enclosed by quotes.

Write to Existing Text File

To write to an existing text file you can use the OpenTextFile method of FileSystemObject with ForWriting mode.

Sub FSOWriteToTextFile() 
    Dim FSO As New FileSystemObject
    Set FSO = CreateObject("Scripting.FileSystemObject")
    Set FileToWrite = FSO.OpenTextFile("C:\Test\TestFile.txt", ForWriting)

    FileToWrite.Write "test line"
    FileToWrite.Close

End Sub

Please note that you do not necessarily need FileSystemObject to write to an existing text file. The above example is shown in another way in this code below (see other example in the Data Range to Text File section):

Sub WriteToTextFile()
    Dim FileName As String
    FileName = "C:\Test\TestFile.txt"

    Open FileName For Output As #1
    Print #1, "test line"
    Close #1

End Sub

Please note that using Write command instead of Print will result in having the added content enclosed by quotes. Having both commands in your macro

Write #1, "test line #1"
Print #1, "test line #2"

will result in a text file like this:

Append to Text File

By changing the mode in the above code to ForAppending, a line can be added to the end of the text file:

Set FileToWrite = FSO.OpenTextFile("C:\Test\TestFile.txt", ForAppending)

WriteLine Method

This method appends the input string as a separate line to the existing content.

Write Method

The input string is appended on the same line as the existing content.

WriteBlankLines

This method takes the number of blank lines to be written to the text file as a parameter.

This code below illustrates the difference between the different write methods:

Sub WriteMethods()
    Dim FSO As New FileSystemObject
    Set FSO = CreateObject("Scripting.FileSystemObject")
    Set FileToWrite = FSO.OpenTextFile("C:\Test\TestFile.txt", ForAppending)

    FileToWrite.Write "test line #1 "
    FileToWrite.Write "test line #2"
    FileToWrite.WriteBlankLines (3)
    FileToWrite.WriteLine "test line #3"
    FileToWrite.WriteLine "test line #4"
    FileToWrite.Close

End Sub

And the result:

Data Range to Text File

If you want to output a data range from your worksheet to a text file, you can use this code:

Sub OutputToTextFile()
    Dim FileName As String, LineText As String
    Dim MyRange As Range, i, j
    
    FileName = "C:\Test\TestFile.txt" 'you can specify here the text file name you want to create
    
    Open FileName For Output As #1
    
    Set MyRange = Range("data") 'it assumes you have a data range named “data” on your worksheet
    For i = 1 To MyRange.Rows.Count
        For j = 1 To MyRange.Columns.Count
            LineText = IIf(j = 1, "", LineText & ",") & MyRange.Cells(i, j) 'the text file creating will have a comma separator
        Next j
        Print #1, LineText 'using Write command instead of Print will result in having your data in quotes in the output text file
    Next i

    Close #1

End Sub

Array to Text File

You can also save your array of data into a text file like this:

Sub SaveArrayToTextFile()
    Dim MyArray As Variant
    Dim FSO As New FileSystemObject
    Set FSO = CreateObject("Scripting.FileSystemObject")

    MyArray = Array(Array("00", "01"), Array("10", "11"), Array("20", "21"))

    Set FileToCreate = FSO.CreateTextFile("C:\Test\TestFile.txt")

    For n = 0 To UBound(MyArray)
        FileToCreate.WriteLine MyArray(n)(0) & "," & MyArray(n)(1)
    Next
    
    FileToCreate.Close

End Sub