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

'-------------------------------------------------------------------------------------
'CHANGE THESE PARAMETERS TO SUIT
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
    Else
        stNextLine = Join$(Application.Transpose(Application.Transpose(rng.Rows(lRow).Value)), stSeparator)
    End If
    If stOutput = "" Then
        stOutput = stNextLine
    Else
        stOutput = stOutput & vbCrLf & stNextLine
    End If
Next lRow

Set fso = CreateObject("ADODB.Stream")
With fso
    .Type = 2
    .Charset = stEncoding
    .Open
    .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.