I'm trying to export my workbook to a text file but my code adds quotes in the exported file if the cells has commas in it.

Below is my code. Any help is appreciated.

Sub ExportFile()

    Dim wb As Workbook
    Dim saveFile As String
    Dim WorkRng As Range
    Dim r As Long

    On Error Resume Next
  

    r = Worksheets("sheet1").Cells(Rows.Count, "A").End(xlUp).Row

    Set WorkRng = Range(Cells(2, 1), Cells(r, 99))


    Application.ScreenUpdating = False

    Application.DisplayAlerts = False

    Set wb = Application.Workbooks.Add

    WorkRng.Copy

    wb.Worksheets(1).Paste

    saveFile = Application.GetSaveAsFilename(InitialFileName:="output", fileFilter:="Text Files (*.txt), *.txt")

    wb.SaveAs Filename:=saveFile, FileFormat:=xlText, CreateBackup:=False

    wb.Close

    Application.CutCopyMode = False

    Application.DisplayAlerts = True

    Application.ScreenUpdating = True

End Sub

Current Output: Current Output

Desired Output: Desired Output

CodePudding user response：

Any of the answers in the link posted in comments above will provide a good solution. For me personally, I most often want a bit more control over the output and usually code my own solution. You choose your own path.... :)

EDIT: added code to delete the "hanging" delimiter on the end of each line, and the "hanging" vbNewLine at the end of the whole buffer.

Option Explicit

Sub ExportThisSheet()
    Dim thisWS As Worksheet
    Set thisWS = Sheet1
    
    Dim lastRow As Long
    Dim workArea As Range
    With thisWS
        lastRow = .Cells(.Rows.Count, 1).End(xlUp).Row
        Set workArea = .Range(.Cells(2, 1), .Cells(lastRow, 99))
    End With
        
    Const DELIMITER As String = vbTab   'change to "    " ?
    Dim workRow As Variant
    Dim textLine As String
    Dim textResult As String
    For Each workRow In workArea.Rows
        textLine = vbNullString
        Dim i As Long
        For i = 1 To workArea.Columns.Count
            '--- optionally check if the Value is empty and skip if you want
            textLine = textLine & workRow.Cells(1, i).Value & DELIMITER
        Next i
        '--- delete the extra DELIMITER
        textLine = Left$(textLine, Len(textLine) - 1)
        textResult = textResult & textLine & vbNewLine
    Next workRow
    '--- delete the last newline
    textResult = Left$(textResult, Len(textResult) - 1)
        
    Dim saveFilename As String
    saveFilename = Application.GetSaveAsFilename(InitialFileName:="output", _
                                                  FileFilter:="Text Files (*.txt), *.txt")
    Open saveFilename For Output As #1
    Print #1, textResult
    Close #1
End Sub