DoCmd.TransferSpreadsheet corrupt excel files

Question

Hi,

I've encountered an issue using TransferSpreadsheet to excel workbook.

I have an Access VBA function that does daily exports, and after adding columns to my Queries all my Excel files are "corrupted".

Repairing works fine (data is ok) but it blocks other scripts that need to open the file automatically.

I investigated inside the XML files to understand what the issue is and found that Access export values where SharedString IDs are expected. Repairing insert my numerical values to sharedstring xml and update the ID (with Excel message: Numerical values stored as text)

Is there a way to force Access to remove the type (t="s") when exporting to an Excel sheet?

Thanks,

Loris

Answer 1

Hi,

You can try using the acSpreadsheetTypeExcel12 instead of acSpreadsheetTypeExcel12Xml-

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12, strTable, strPath & strWorkbook

Without using the common string table, Access will export the values directly as numeric or text values. This will help to avoid the problem of the "corrupted" material and the requirement for repair.

There may be an increase in your file size.

Try this code once and let me know if it works.

Best Regards.