Excel VBA Save as tab delimited file

Question

I have some code that saves the active worksheet as a tab delimited text file, however the cell data that has a comma character in them gets exported with quotation marks, like

John Hopkins, Burgers
becomes
"John Hopkins, Burgers"

How can I work around this?

This is my code:

ActiveWorkbook.SaveAs ActiveWorkbook.path & "\" & filename,
FileFormat:=xlText, CreateBackup:=False

Adding this: I just found out that if I save the file again it removes all "". Can I add an additional save to the code?

Answer 1

I solved it! Since the quotes disappear when you save it manually as a delimited file but not when you use

ActiveWorkbook.SaveAs ActiveWorkbook.path & "\" & filename,
FileFormat:=xlText, CreateBackup:=False

I just added another save after the above one, but this time using the sendkey strokes to mimick the manual method.

ActiveWorkbook.SaveAs fil, FileFormat:= _ xlText

SendKeys "^{F4}"
SendKeys "{ENTER}"
SendKeys "{ENTER}"

Answer 2

According to FileFormat Property, you have the following formats to choose from:

xlCSV
xlCSVMac
xlCSVMSDOS
xlCSVWindows

Why don't you choose one of the comma- separated value formats?

Try xlCSV.