TransferSpreadsheet builds invalid XLSX file if user customized decimal separator

Question

In Microsoft Access 2016 (build 16.0.8201.2200), the VBA TransferSpreadsheet method is not working properly when the format of numbers in Windows 10 is customized, specifically, on computer with US region selected, if you swap "decimal symbol" and "digit grouping symbol" to be formatted like customary in Germany:

When I use TransferSpreadsheet to save a query, when I subsequently attempt to open that workbook in Excel, it says:

We have found some problem in some content in '...'. Do you want us to try to recover as much as we can?

When I do, I get the following warning:

Excel was able to open the file by repairing or removing the unreadable content.

When I look at the contents of the XLSX contents, I'm not surprised it's having a problem, because the internal XML is not well-formed. Because I've replaced the decimal separator to be "," in Windows, it's creating the numbers in the XML with commas, not decimal places. But XML standards dictate that regardless of your regional preferences, numbers in XML should use a "." as decimal symbol.

<worksheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main" xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships">
  <dimension ref="A1:K20"/>
  <sheetViews>...</sheetViews>
  <sheetFormatPr defaultRowHeight="15"/>
  <sheetData>
    <row outlineLevel="0" r="1">...</row>
    <row outlineLevel="0" r="2">
      ...
      <c r="D2" s="0">
        <v>2,9328903531E+16</v>
      </c>
      <c r="E2" s="0">
        <v>5,404939826E+16</v>
      </c>
      <c r="F2" s="0">
        <v>2,3923963705E+16</v>
      </c>
      ...
    </row>
    ...
  </sheetData>
  <pageMargins left="0.7" right="0.7" top="0.75" bottom="0.75" header="0.3" footer="0.3"/>
</worksheet>

While the "," might be the desired format for decimal symbol in the UI, the XLSX internal format must conform to XML standard, "." decimal symbol.

How do I solve this?

Answer 1

Bottom line, for the TransferSpreadsheet method to work correctly, if you want to change the formatting of numbers, do not use the "Customize Format" setting:

You should instead reset those values back to their defaults, and then select an appropriate region in the preceding dialog box, one that formats numbers as you prefer:

Having choosen a region that is formatted as desired, you thereby avoid the TransferSpreadsheet bug. When you do this, the spreadsheet will appear correctly in Excel:

But the XLSX will be formatted properly, too:

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<worksheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main" xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships" xmlns:mc="http://schemas.openxmlformats.org/markup-compatibility/2006" mc:Ignorable="x14ac" xmlns:x14ac="http://schemas.microsoft.com/office/spreadsheetml/2009/9/ac">
  <dimension ref="D3:F3"/>
  <sheetViews>
    <sheetView tabSelected="1" workbookViewId="0">
      <selection activeCell="F12" sqref="F12"/>
    </sheetView>
  </sheetViews>
  <sheetFormatPr defaultRowHeight="15" x14ac:dyDescent="0.25"/>
  <cols>
    <col min="4" max="6" width="26.85546875" style="1" bestFit="1" customWidth="1"/>
  </cols>
  <sheetData>
    <row r="3" spans="4:6" x14ac:dyDescent="0.25">
      <c r="D3" s="1">
        <v>2.9328903531E+16</v>
      </c>
      <c r="E3" s="1">
        <v>5.40493826E+16</v>
      </c>
      <c r="F3" s="1">
        <v>2.3923963705E+16</v>
      </c>
    </row>
  </sheetData>
  <pageMargins left="0.7" right="0.7" top="0.75" bottom="0.75" header="0.3" footer="0.3"/>
</worksheet>