Opening CSV file has warning message

[kweaver]

I save a sheet via VBA as a CSV file. When I try to open it in Excel, I get a warning message that the format and extension of the file didn't match.
I have the option to open it anyway. But, how do I avoid getting this warning?

The CSV file when open has the correct file name. However, there are two tabs. The first one is the one I want. The 2nd one is labeled "Sheet1".
Should I delete that sheet before saving via VBA?

 

[John_w]

Sounds like you've saved it as a workbook with .csv extension. Saving as CSV should be something like this:

    Worksheets("Sheet1").Copy
    ActiveWorkbook.SaveAs ThisWorkbook.Path & "/Sheet1.csv", FileFormat:=xlCSV
    ActiveWorkbook.Close False

 

[kweaver]

I saved it like this:

zNewWB.SaveAs sSaveAsFilePath

Where

?zNewWB.Name
TimeSheet_07222020 11 58.csv

and

?sSaveAsFilePath
TimeSheet_07222020 11 58.csv

Shouldn't that be OK?

 

[kweaver]

When I used your approach, the CSV was saved, and opening didn't cause a warning. However, I had two columns that were text with leading zeros and they were turned into their number values without the leading zeros and no longer formatted as text.

 

[Fluff]

If you open the csv in a text editor, are the leading 0s still there?

 

[kweaver]

YES! I'll have to have the user do that.

 

[Joe4]

YES! I'll have to have the user do that.

I advise my users NEVER to open a CSV file in Excel if they want to see what is really contained in the CSV, as opening it in Excel does not give a true depiction of the file contents. Excel performs it own conversion on the data, when it tries to "guess" what the format of each value is (they sometimes guess wrong!).