Export file to text file without quotes

Question

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

This is the expected behavior, because each cell is represented as comma-delimited values. What do you expect the output to look like and can you provide a simple example? — PeterT, Mar 29 '22 at 19:05
@PeterT I was hoping to export it to a text file without quotes. Just added an example of my current output and my desired output. — joe, Mar 29 '22 at 19:14
Does this answer your question? [Saving a Excel File into .txt format without quotes](https://stackoverflow.com/questions/11501531/saving-a-excel-file-into-txt-format-without-quotes) — Aubs, Mar 29 '22 at 19:33

PeterT · Accepted Answer · 2022-03-29T21:33:16.227

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

Thank you Peter. This exports it without the quotes but it now adds an extra column at the end and also a blank row at the end than the specified range. Any ideas ? — joe, Mar 29 '22 at 21:19

Export file to text file without quotes

1 Answers1