How to force quotes in every field while saving an excel worksheet as a csv file?

Question

Here is my situation.

data = "" & data & ""
xlsheet.Range(crange).Value = data
xlfile.SaveAs(FileName:=finalfilename, FileFormat:=6)

This code puts " around the data, puts the data in the range and saves the workbook(xlfile) with one worksheet(xlsheet) as a csv file.

If I open the csv file in notepad I find these following

1.quotes are removed
2.leading 0 from numbers are removed

How to ensure that quotes are present while saving the file programatically? Thanks in advance!

Answer 1

This line is not doing anything:

' Append empty strings on front and back (i.e., do nothing). '
data = "" & data & ""

Try this:

' Append double quotes on front and back. '
data = """" & data & """"

Does that help?

---

UPDATE: I could be off-base here, but I think the problem may simply be that you don't have enough fine-grained control over what Excel is doing when it saves a CSV file. As I recall, by default it does not place quotes around values unless they are needed to qualify a discrete cell that may contain data that would otherwise mess up the CSV format. For example, it puts quotation marks around cells with text values that include commas.

Presumably it also converts single quotes in text values with double quotes in the CSV file and encloses text values containing quotation marks with, well, quotation marks.

The most obvious way of getting around this, to me, would be simply to save the data to disk yourself. This way you can manually put quotation marks around every cell and not worry about what voo doo Excel may be performing automatically on the SaveAs call.

Obviously, it's annoying because it's more work. But it's the only idea that's coming to my mind at the moment. Anyway, it shouldn't be that much work.