How to prevent additional inverted comas duplicated when saving Excel file as txt

Viewed 35

I have a Excel file that I want to save as text, but I noticed that the cells that have double inverted comas (") get the double inverted comas duplicated and also these cells get double inverted comas at the beginning/end of the text.

For example

A1 = Cell with inverted coma (")
A2 = No Inverted coma

I'm using the following code:

Sheets("SO").Copy
Cells.Copy
txtFile = "C:\TextSO.txt"
ActiveWorkbook.SaveAs Filename:=txtFile, FileFormat:=xlUnicodeText
ActiveWindow.Close

The output I get in the txt file is as follows:

"Cell with inverted coma ("")"
No Inverted coma

I tried also with FileFormat:=xlTextMSDOS but there was no difference

I tried also creating a txt file adding the row values, but didn't help. This is the code I used:

txtFile = "C:\TextSO.txt"
LR = ActiveSheet.Range("A1").Offset(ActiveSheet.Rows.Count - 1, 0).End(xlUp).Row
Open txtFile For Output As #1
For I = 1 To LR
    Write #1, Range("A" & I).Value
Next
Close #1

The output I got is as follows:

"Cell with inverted coma ("")"
"No Inverted coma"

Any ideas on what code I can use to prevent the additional inverted comas to be added?

1 Answers

Write adds quotes, so use Print:

Print #1, Range("A" & I).Value
Related