How do i remove quotations in text file export. (1 Viewer)

[camiramzi]

Hi i'm using this vba code to convert excel sheets to txt file

        Sub ExportSheetsToText()
            Dim xWs As Worksheet
            Dim xTextFile As String
               For Each xWs In Application.ActiveWorkbook.Worksheets
                 xWs.Copy
                 xTextFile = CurDir & "\" & xWs.Name & ".txt"
                 Application.ActiveWorkbook.SaveAs Filename:=xTextFile, FileFormat:=xlText
                 Application.ActiveWorkbook.Saved = True
                 Application.ActiveWorkbook.Close
              Next
          End Sub

i have a problem with the VBA for export to text file, as the VBA run the txt file got generated, but the inside data is not exactly like the excel data, for example my data include character quotation mark, and the exported data became double quotation mark, plus the beginning and the ending of the text file there are quotation mark,

i tried different VBA code but i ran to the same problem any help thank you.

 

[Isaac]

Hi i'm using this vba code to convert excel sheets to txt file

        Sub ExportSheetsToText()
            Dim xWs As Worksheet
            Dim xTextFile As String
               For Each xWs In Application.ActiveWorkbook.Worksheets
                 xWs.Copy
                 xTextFile = CurDir & "\" & xWs.Name & ".txt"
                 Application.ActiveWorkbook.SaveAs Filename:=xTextFile, FileFormat:=xlText
                 Application.ActiveWorkbook.Saved = True
                 Application.ActiveWorkbook.Close
              Next
          End Sub

i have a problem with the VBA for export to text file, as the VBA run the txt file got generated, but the inside data is not exactly like the excel data, for example my data include character quotation mark, and the exported data became double quotation mark, plus the beginning and the ending of the text file there are quotation mark,
View attachment 112386
i tried different VBA code but i ran to the same problem any help thank you.

I think possibly if you save it as CSV you will accomplish what you are after.

 

[camiramzi]

I think possibly if you save it as CSV you will accomplish what you are after.

I get the same problem in CSV ,
is there a VBA code to prevent quotation from appearing in the beginning of every paragraph.

 

[arnelgp]

i think that is normal for your excel with delimiter as ".
try exporting the text file to another db.
does it remove the extra quote. i think so.

 

[camiramzi]

i think that is normal for your excel with delimiter as ".
try exporting the text file to another db.
does it remove the extra quote. i think so.

i don't understand what do you mean by exporting the text file to another db.

 

[arnelgp]

here is a demo. run your export routine, then run the import_csv and import the txt file (will be imported on sheet2)

 

[camiramzi]

i think that is normal for your excel with delimiter as

i found this answer in quora
When i remove the commas inside the paragraphs the quotation disappear in the export text

 

[arnelgp]

When i remove the commas inside the paragraphs the quotation disappear in the export text

then, that won't be the Original text..
because you imported it with quotes, then you expect it to be imported back with quotes also.

 

[Isaac]

i found this answer in quora
When i remove the commas inside the paragraphs the quotation disappear in the export text

That's not magic - it's because of CSV ANSI specs, which hence my suggestion.

Anytime you have commas appearing organically in the actual text values, you must quote the columns to be CSV-compliant.

If you don't, if you forcibly remove those quotes from the text somehow (easy, but why would you?) - then you'll have no way to denote columns.

The quotes are your friend