three quotation marks in from Excel (VBA) exported .txt-file

Question

I have the following problem: To save the Worksheet in a .txt file, I wrote this sub:

Sub SaveFile()

Dim ans As Long
Dim sSaveAsFilePath As String
Dim VPname As String

VPname = Worksheets(3).Cells(2, 1)

    On Error GoTo ErrHandler:

    sSaveAsFilePath = ActiveWorkbook.Path & "\" & VPname & ".txt"

    If Dir(sSaveAsFilePath) <> "" Then
        ans = MsgBox("Datei " & sSaveAsFilePath & " existiert bereits.  Überschreiben?", vbYesNo + vbExclamation)
        If ans <> vbYes Then
            Exit Sub
        Else
            Kill sSaveAsFilePath
        End If
    End If
    Worksheets(3).Copy '//Copy sheet 3 to new workbook
    ActiveWorkbook.SaveAs sSaveAsFilePath, xlTextWindows '//Save as text (tab delimited) file
    If ActiveWorkbook.name <> ThisWorkbook.name Then '//Double sure we don't close this workbook
        ActiveWorkbook.Close False
    End If
MsgBox ("Worksheet wurde erfolgreich als txt-Datei gespeichert!")
My_Exit:
Exit Sub

ErrHandler:
MsgBox Err.Description
Resume My_Exit

End Sub

In this worksheet, the cells with text content have to have quotation marks (e.g. "example"). When I open the .txt-file, all these entrys have three quotation marks instead of one ("""example"""). Do you know how to fix this? Thanks a lot :)

Are you sure you **want** to fix it. The two quotation marks at either end are showing that the field is a string, and the two "pairs" are the escaped double quotation marks indicating that your string has a double quotation mark at each end of your actual string. If the string in your file only contains one double quotation mark at each end, that implies that the string being represented is just `example` rather than `"example"`. — YowE3K, Mar 17 '17 at 10:00

score 1 · Answer 1 · edited May 23 '17 at 12:00

Welcome to stackoverflow! It is really useful, to make a small question, that is replicable by the others - read more here - https://stackoverflow.com/help/mcve Thus, it would be easy for you to search as well.

In your case, a minimal and verifyable example would be like this:

Option Explicit

Public Sub TestMe()

    ActiveWorkbook.SaveAs ThisWorkbook.Path & "\new1.txt", _
    FileFormat:=xlTextWindows

End Sub

Then, if you use the search engine, you would find that there is already some answer of it here:

Saving a Excel File into .txt format without quotes

:)

three quotation marks in from Excel (VBA) exported .txt-file

1 Answers1