Using VBA to Write Excel Data to Text File

It’s a fairly common requirement to need a bit of VBA code to write Excel data to a text file. ┬áThere are several methods you can use to do this, but here’s an example which I like to use, as it offers good control over the range of data, the structure and format of the text output and – unusually – control over the text encoding / character set. It is really simple to specify UTF-8, UTF-16, ASCII, ISO8859, etc.

Sub WriteTextFile()

Dim rng As Range, lRow As Long
Dim stOutput As String, stNextLine As String, stSeparator As String
Dim stFilename As String, stEncoding As String
Dim fso As Object

Set rng = ActiveSheet.UsedRange 'this is the range which will be written to text file
stFilename = "C:\Temp\TextOutput.txt" 'this is the text file path / name
stSeparator = vbTab 'e.g. for comma seperated value, change this to ","
stEncoding = "UTF-8" 'e.g. "UTF-8", "ASCII"

For lRow = 1 To rng.Rows.Count
    If rng.Columns.Count = 1 Then
        stNextLine = rng.Rows(lRow).Value
        stNextLine = Join$(Application.Transpose(Application.Transpose(rng.Rows(lRow).Value)), stSeparator)
    End If
    If stOutput = "" Then
        stOutput = stNextLine
        stOutput = stOutput & vbCrLf & stNextLine
    End If
Next lRow

Set fso = CreateObject("ADODB.Stream")
With fso
    .Type = 2
    .Charset = stEncoding
    .WriteText stOutput
    .SaveToFile stFilename, 2
End With
Set fso = Nothing

End Sub

Change the parameters to suit your needs – you can use any code you like in this section, as long as the four variables rng, stFilename, stSeparator and stEncoding are assigned.


Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.