Write Excel Data to Text File – Early Binding

Following on from my last post Using VBA to Write Excel Data to Text File – I’ve had this reply from Bernie Deitrick, Excel MVP, who has written a modified version of the code for when early binding is preferred:

Sub WriteTextFileEarlyBinding()
    Dim rng As Range
    Dim lRow As Long
    Dim strOutput As String
    Dim strNextLine As String
    Dim strFilename As String
    Dim strSeparator
    Dim adStream As ADODB.Stream

    strFilename = ThisWorkbook.Path & "\Output.txt"
    Set rng = ActiveSheet.UsedRange    'Selection
    strSeparator = vbTab   ' e.g. for comma seperated value, change this to strSeparator = ","

    For lRow = 1 To rng.Rows.Count
        strNextLine = Join$(Application.Transpose(Application.Transpose(rng.Rows(lRow).Value)), strSeparator)
        If strOutput = "" Then
            strOutput = strNextLine
            strOutput = strOutput & vbCrLf & strNextLine
        End If
    Next lRow

    Set adStream = New ADODB.Stream

    With adStream
        .Type = adTypeText  ' or use adTypeBinary
        .Charset = "us-ascii"    'See table below
        .WriteText strOutput
        .SaveToFile strFilename, adSaveCreateOverWrite    ' 2; adSaveCreateNotExist = 1
    End With

End Sub

'Constant            Value
'CdoBIG5            "big5"
'CdoEUC_JP          "euc-jp"
'CdoEUC_KR          "euc-kr"
'CdoGB2312          "gb2312"
'CdoISO_2022_JP     "iso-2022-jp"
'CdoISO_2022_KR     "iso-2022-kr"
'CdoISO_8859_1      "iso-8859-1"
'CdoISO_8859_2      "iso-8859-2"
'CdoISO_8859_3      "iso-8859-3"
'CdoISO_8859_4      "iso-8859-4"
'CdoISO_8859_5      "iso-8859-5"
'CdoISO_8859_6      "iso-8859-6"
'CdoISO_8859_7      "iso-8859-7"
'CdoISO_8859_8      "iso-8859-8"
'CdoISO_8859_9      "iso-8859-9"
'cdoKOI8_R          "koi8-r"
'cdoShift_JIS       "shift-jis"
'CdoUS_ASCII        "us-ascii"
'CdoUTF_7           "utf-7"
'CdoUTF_8           "utf-8"
'The contents of Charset are case-insensitive. The default value is "us-ascii".

Code posted with his permission. Thanks, Bernie!

Leave a Reply

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