Date reversed when saving Excel workbook with macro

Question

I have a CSV file with a column of dates in US format (my regional formatting is UK). If I open the CSV file in excel and then save the file manually it modifies the date entries slightly (if recognised as valid UK dates) but saves them in the same format. However, if I save the workbook with a macro using ActiveWorkbook.Save the dates are reversed (the ones that are recognised as valid UK dates that is).

What the heck is going on? How do I save with a macro and avoid this?

Example input file:

Date
06/02/2020
06/30/2020
07/13/2020
07/22/2020
07/31/2020
08/11/2020
08/11/2020
08/31/2020
09/22/2020
09/29/2020

After manually saving:

Date
6/02/2020
06/30/2020
07/13/2020
07/22/2020
07/31/2020
8/11/2020
8/11/2020
08/31/2020
09/22/2020
09/29/2020

After saving with macro:

Date
2/6/2020
06/30/2020
07/13/2020
07/22/2020
07/31/2020
11/8/2020
11/8/2020
08/31/2020
09/22/2020
09/29/2020

Solution: The solution as pointed out in Ron's answer was to replace the Save line with SaveAs setting the Local variable to True so it didn't use the VBA US region default.

    Application.DisplayAlerts = False
    ActiveWorkbook.SaveAs Filename:=ActiveWorkbook.FullName, Local:=True
    Application.DisplayAlerts = True

Ron Rosenfeld Ron Rosenfeld · Accepted Answer · 2020-10-27T00:28:14

Your problem stems from opening the csv file in your version of Excel.

When you open the file, Excel assumes any dates will have the same short-date format as your Windows Regional Settings.

Since your WRS is DMY and the csv file dates are MDY

Dates where the 2nd part is <=12 will be interpreted incorrectly as DMY
Dates where the 2nd part is >12 will be retained as text

To prevent this from happening, before the CSV file gets into Excel (gets written to an Excel worksheet), you must tell Excel the format of the incoming dates.

One method (and the best in my opinion) is to import the CSV file, using either the legacy wizard or Power Query. When you do that you will be afforded an opportunity to tell Excel the format of the incoming data.

A workaround is to create the CSV with unambiguous dates. yyyy-mm-dd usually works, as might dd-mmm-yy although the latter will have problems with language differences where month abbreviations are different.

To have the dates saved in the same format, you could

import the dates, then format that column, in Excel, as mm/dd/yyy before saving it again.
import the dates but, during the import process, designate that column as text. This will only work if that column will not be modified, or used for any computations, while the workbook is open.

EDIT

Another possibility, which might help in your specific example, might be to set the Local parameter of the Workbook.SaveAs method to True, so the file is saved in the language/settings of Excel, rather than VBA. Although doing so might work in this instance, it is far from bulletproof and might cause problems down the road depending on what the end user wants to do with these dates. I am guessing that when SaveAs is executed from the Excel worksheet, it will save using the language/settings of Excel (I have not seen documentation for this); whereas VBA being US-centric, it is saving in the language of VBA (ie: USA) unless Local is specified.

Date reversed when saving Excel workbook with macro

1 Answers