VBA: Save Excel-Table as CSV (semicolon delimited)

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
Advertisements

17 Responses to VBA: Save Excel-Table as CSV (semicolon delimited)

  1. Gosa says:

    This is really usefull information.
    Thanks a lot

  2. Guest says:

    You’re good man! Short and illustrative!

  3. Marino says:

    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 😦

  4. Djinn says:

    Works great… !!!!! Thanks !!!!

    • MB says:

      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 ?

      • logbuffer says:

        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.

  5. Marino says:

    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)

    • logbuffer says:

      Your code is this:

      Sub test()
      
      Dim MyFile As String
      MyFile = Application.DefaultFilePath & "\test.csv"
      Application.DisplayAlerts = False
      
      Sheets(1).Activate
      ActiveSheet.Copy
      
      ActiveWorkbook.SaveAs Filename:=MyFile, FileFormat:=xlCSV, CreateBackup:=False, Local:=True
      
      ActiveWorkbook.Close True
      
      Application.DisplayAlerts = True
      
      End Sub
      

      The cause of your problem is this line:

      ActiveWorkbook.Close True

      If 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.

      • Marino (MB) says:

        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.

      • deville says:

        u’re a genius!!!! Thnx a lot!!!!!

  6. Rob Bronckers says:

    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.

  7. Henry Chinasky says:

    Txn u very very much!
    You save me!

  8. ian says:

    I love you. It works!!!

  9. TimCo says:

    Great Tips. Thanks you. The comment about ActiveWorkbook.Close save me a lot of time too 🙂

  10. PasiniK says:

    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!

  11. You made my day! I’ve stuck with these problem for two days. Thank you very much.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: