VBA: Save Excel-Table as CSV (semicolon delimited)
October 19, 2010 20 Comments
To save a MS-Excel-table as a plain ASCII CSV-File is easy when you do it manually. No matter where you are. You go via menu FILE>SAVE AS> and select CSV as filetype.
But when your local (european) system uses the comma (“,”) as the decimal separator (you need semicolon as field delimiter in CSV) and you record this steps as a VBA-macro and execute it, you’d be surprised of the output.
Your CSV-file is now _really_ a comma-separated-value-file – while having the semicolon as the CSV-delimiter when saving manually. And to make things worse even your “european -style” date-type-fields (dd.mm.yyyy) are stored in the american notation (mm/dd/yyyy):
10/11/2010,9:15,12:30,13:00,18:30,W 10/12/2010,9:00,18:00,,,W 10/13/2010,9:20,12:30,13:00,17:30,W 10/15/2010,8:45,12:30,13:00,18:00,W
Obviously your regional-settings are completely ignored. At MS you find a not really helpfull advice to store your data manually as the given behaviour is intended because it “ensures that the result of running macro code is consistent regardless of the computer’s regional settings.”. Not that satisfactorily, is it?!
At first I tried to use a procedure that merges the fields semicolon delimited. But that way I lost my time formats:
11.10.2010;0,385416666666667;0,520833333333333;0,541666666666667;0,770833333333333;W 12.10.2010;0,375;0,75;;;W 13.10.2010;0,388888888888889;0,520833333333333;0,541666666666667;0,729166666666667;W 15.10.2010;0,364583333333333;0,520833333333333;0,541666666666667;0,75;W
Ok, even when MS says that this CSV-problem applies to Excel 97, 2000 and 2002 I found a hint in my Excel 2002-Online-Help to use the parameter “Local:=true” in the save-statement:
ActiveWorkbook.SaveAs Filename:=fnameProject, FileFormat:=xlCSV, CreateBackup:=False, Local:=True
This makes Excel to obey to your regional settings regarding decimal-separator and date-format.
11.10.2010;09:15;12:30;13:00;18:30;W 12.10.2010;09:00;18:00;;;W 13.10.2010;09:20;12:30;13:00;17:30;W 15.10.2010;08:45;12:30;13:00;18:00;W
Logbuffer-Blog 
This is really usefull information.
Thanks a lot
15 HORAS DANDO VUELTAS, HASTA QUE TE ENCONTRE, GRACIAS
A de Fe ( Guadalajara_Spain)
You’re good man! Short and illustrative!
Unfortunately,
It won’t work in my case. I even checked my regional setting, and separator is set to semicolon, but excel still applies comma 😦
Works great… !!!!! Thanks !!!!
Hi,
I have a same problem with macro used to save Excel as CSV semicolon delimited. Unfortunately this use of the parameter “Local:=true” in the save-statement does not helping me. I still get colon delimited file. I checked my regional settings and as separator there is semicolon, so it should be working fine, according to your advice. But it won’t work for me. I use Windows 7 and Excel 2010. Any suggestion what is wrong in my case ?
I tested it under Windows XP and Excel 2010 with german regional-settings – and this works. Are you sure, that in your regional settings the list-separator is set to semicolon? Maybe you confused the decimal separator with the list separator?! I wonder why your separator is a colon (“:”). This would be a really unusual setting. In Excel you can check the settings under FILE>OPTIONS>LANGUAGE and FILE>OPTIONS>ADVANCED. The latter has a checkbox for “apply separator from OS” (or so); maybe this could help.
Sorry, when I said that my separator is colon (“:”), I actually meant on comma(“,”). It was my misspelling.
However, I checked my Excel setings and it is set to “Use system separator”, and in Windows Regional Settings (I use Windows 7) under Region and Language>Formats>Additional settings>Numbers>List Separator, stands “;” (semicolon).
When I use macro, despite my statement is Local = True, I allways get CSV with comma.
On the other side, when I do ti manually it works fine, I get a CSV file with semicolon.
Please, take a look on my example, this is the link where you can take it: (link deleted)
Your code is this:
The cause of your problem is this line:
ActiveWorkbook.Close TrueIf you step through your macro in VB-Editor with F8 and check the created file test.csv right after the save-operation, you would see a semicolon separated file. But just after the closing of the workbook with “ActiveWorkbook.Close True” the separator of that file changes to comma. The culprit is the “True” at the “ActiveWorkbook.Close”. This “True” referes to the parameter “SaveChanges”. I don’t know why, but despite you have just saved the workbook (to CSV), Excel suspects some changes to the workbook and saves it once more. As you have saved it as CSV-file, Excel regards this CSV-file as the new workbook. And because of the “SaveChanges := True” it saves it once more to your CSV-file – but this time without “Local := True”. And hence the change of separator from semicolon to comma.
Thank you very, very much!
That’s it 🙂 Now it works fine. Indeed the problem was in this “True” part of “ActiveWorkbook.Close” line, as you remarkably noticed.
u’re a genius!!!! Thnx a lot!!!!!
This ActiveWorkbook.Close command should be followed by False. This will close the workbook without overwriting it with a “,” separator. Leaving True or False out will produce unpredictable results. (windows 7 ultimate en excel 2010)
so us “Active.Workbook.Close False” for proper result.
Txn u very very much!
You save me!
I love you. It works!!!
Great Tips. Thanks you. The comment about ActiveWorkbook.Close save me a lot of time too 🙂
Been struggling with this for days. Commas embedded in my resultant CSV file, regardless of Regional and Language settings. Adding this switch solved the problem. Thank you!
You made my day! I’ve stuck with these problem for two days. Thank you very much.
Muchas Gracias tu solución fue de gran ayuda ¡¡¡¡¡
Why didn’t I find this page any sooner? Thanks a lot for this!!!!
Thanks a lot man!!!! It works great!!!!!!