Opening CSV file has warning message

kweaver

Well-known Member
Joined
May 8, 2018
Messages
1,565
Office Version
  1. 365
  2. 2010
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?
 

Some videos you may like

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.

John_w

MrExcel MVP
Joined
Oct 15, 2007
Messages
6,707
Sounds like you've saved it as a workbook with .csv extension. Saving as CSV should be something like this:
VBA Code:
    Worksheets("Sheet1").Copy
    ActiveWorkbook.SaveAs ThisWorkbook.Path & "/Sheet1.csv", FileFormat:=xlCSV
    ActiveWorkbook.Close False
 

kweaver

Well-known Member
Joined
May 8, 2018
Messages
1,565
Office Version
  1. 365
  2. 2010
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

Well-known Member
Joined
May 8, 2018
Messages
1,565
Office Version
  1. 365
  2. 2010
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,301
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

kweaver

Well-known Member
Joined
May 8, 2018
Messages
1,565
Office Version
  1. 365
  2. 2010
YES! I'll have to have the user do that.
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,512
Office Version
  1. 365
Platform
  1. Windows
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!).
 

Watch MrExcel Video

Forum statistics

Threads
1,127,099
Messages
5,622,700
Members
415,922
Latest member
gemmatay88

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top