MS Access export to Excel breaks the Excel file when the decimal and thousand separator have been swapped

Question

I changed a setting in a GPO to swap the decimal symbol to the comma and thousand separator to a full stop:

This is so when some old MS Access app generates Excel and Word reports, the numbers are formatted correctly for our European users. After making the swap, I ran a report from Access with 1 column that had no data in it. When I opened the Excel document, I got an error stating that the data was corrupted:

Upon clicking yes, the document opens. However when it's a full blown report, that "fix" breaks charts and macros. It seemed odd to me that a doc with no data could be corrupted. So I looked at the XML of the sheet and found that it was setting the size of something to a value that Excel couldn't handle. Excel was expecting a full stop for a decimal instead of a comma.

I even checked Excel and it said it was using the system settings. So I changed them to explicitly use the comma:

Still no luck. When I change the 15,125 to 15.125, Excel works, despite all of the regional changes I have made. Any ideas what's going on here?

Answer 1

The problem was that Access pulls the decimal symbol based on your format region, even if you override it. When it was writing the XML data, it pulled the comma instead of the period. And Excel's XML doesn't know how to handle the comma, regardless of your settings.

When I changed the format to a country that supports the comma as a decimal symbol, Access was able to write the XML with a full stop.

Answer 2

For those users I would do a checkup in case a custom field was configured.

Can you validate if you replace the PERSONAL.XLSB in C:\Users\user name\AppData\Local\Microsoft\Excel\XLStart, take one on a computer where it work, and check if it help Excel to respect your locale settings.

If yes, you could deploy the file by GPO to the others users