How to Write Data to Text File Using Excel VBA

Excel VBA is a popular solution when dealing with data processing tasks and clean-ups. Moreover, it can automate repetitive tasks that would take much time to do.

One of the key capabilities of VBA is its ability to generate and edit text files without the need to open, name, and save changes manually.

This article will demonstrate how to generate text (.txt) files with the help of VBA.

In this article, we are trying to generate text files using Excel VBA. Before we go deep down with the code, we need to enable Excel VBA to interact with FileSystemObject, which handles files and folders.

Steps for enabling FileSystemObject:

You are now all set.

The code block below will demonstrate creating a text file using VBA.

The SaveTextToFile subroutine requires two parameters, the fileName and the fileContent. The fileName variable will be the desired file name of the text file to be generated. fileContent variable will be the actual content of the text file. The filePath should be editted so it would fit with the correct location of the output file.

It is recommended to declare the FileSystemObject as fso to enable the auto-completion by Intellisense to work which could avoid typographical errors and would also help discover other method within the FileSystemObject library.

It is important to include fso = Nothing and Set fileStream = Nothing before the code execution ends as errors will occur during runtime if these two are not closed.

Option Explicit

Public Sub SaveTextToFile(fileName As String, fileContent As String)
    
    Dim filePath, fileAttributes As String
    filePath = "C:\Users\temp\Desktop\Destination"
    fileAttributes = filePath & "\" & fileName & ".txt"
   
    Dim fso As FileSystemObject
    Set fso = New FileSystemObject
    Dim fileStream As TextStream

    Set fileStream = fso.CreateTextFile(fileAttributes)
    fileStream.WriteLine fileContent
    fileStream.Close

    If fso.FileExists(fileAttributes) Then
        Debug.Print "File Created Successfully."
    End If

    Set fileStream = Nothing
    Set fso = Nothing

End Sub

Sub testSub()

Call SaveTextToFile("sample1", "Here is the content.")

End Sub

The code block above does not actually interact with Excel and is generating a single file only.

The code block below will demonstrate creating text files depending on the data from the Excel file. Refer to Sheet1 for the sample values.

Sheet1:

 |     A       |              B                |          C        |
1| File Name   | Content                       | GenerateTextFile? |
2| sample1     | This is the content of sample1|        Yes        |
2| sample2     | This is the content of sample2|        No         |
2| sample3     | This is the content of sample3|        Yes        |
2| sample4     | This is the content of sample4|        No         |
2| sample5     | This is the content of sample5|        Yes        |
2| sample6     | This is the content of sample6|        No         |

On the example below, we will reuse the SaveTextToFile subroutine for the creation of the text file. The testSub subroutine will call SaveTextToFile in a loop depending on the data of Sheet1.

Option Explicit

Public Sub SaveTextToFile(fileName As String, fileContent As String)
    
    
    Dim filePath, fileAttributes As String
    filePath = "C:\Users\temp\Desktop\Destination"
    fileAttributes = filePath & "\" & fileName & ".txt"
   
    Dim fso As FileSystemObject
    Set fso = New FileSystemObject
    Dim fileStream As TextStream

    Set fileStream = fso.CreateTextFile(fileAttributes)

    fileStream.WriteLine fileContent

    fileStream.Close

    If fso.FileExists(fileAttributes) Then
        Debug.Print fileName & " Created Successfully."
    End If

    Set fileStream = Nothing
    Set fso = Nothing

End Sub

Sub testSub()

Dim wb As Workbook
Dim s1 As Worksheet

Set wb = ThisWorkbook
Set s1 = wb.Sheets("Sheet1")

Dim i As Integer

i = 2

Do Until s1.Cells(i, 1) = ""
    
    If s1.Cells(i, 3) = "Yes" Then
    
    Call SaveTextToFile(s1.Cells(i, 1), s1.Cells(i, 2))
    End If
    
i = i + 1

Loop
End Sub

testSub Output:

sample1 Created Successfully.
sample3 Created Successfully.
sample5 Created Successfully.

Sample1, Sample3, and Sample5 are the text files created as they have Yes in the GenerateTextFile column. Sample2 and Sample4 are not created as they are No in GenerateTextFile column.