I'm using this code to transfer data in from a query to an excel .xlsx spreadsheet
This works over 95 percent of the time. But sometimes, for some reason, the spreadsheet does not update itself. There are no errors and the spreadsheet opens fine just like always, but still contains the old data.
To fix this, I have to either replace the spreadsheet with a backup or rename it and export to a new spreadsheet (then change the name back) This does not happen when exporting to .xls form using acSpreadsheetTypeExcel9.
Has anyone seen this type of behavior? I stopped using the .xls for the limitation on the size of the data transfer. But I very rarely go to the limits of the .xls transfer - so I may go back to that format if I can't figure out what's causing this.
Code:
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, WbSheet, CurrentProject.Path & "\" & WbName, True
This works over 95 percent of the time. But sometimes, for some reason, the spreadsheet does not update itself. There are no errors and the spreadsheet opens fine just like always, but still contains the old data.
To fix this, I have to either replace the spreadsheet with a backup or rename it and export to a new spreadsheet (then change the name back) This does not happen when exporting to .xls form using acSpreadsheetTypeExcel9.
Has anyone seen this type of behavior? I stopped using the .xls for the limitation on the size of the data transfer. But I very rarely go to the limits of the .xls transfer - so I may go back to that format if I can't figure out what's causing this.