transferspreadsheet sometimes has strange behavior (1 Viewer)

Danick · Jul 19, 2019

I'm using this code to transfer data in from a query to an excel .xlsx spreadsheet

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.

theDBguy · Jul 19, 2019

Hi. I can't test this right now but try using your code while the spreadsheet is open just to verify if it will update or not. If it doesn't, then my guess is perhaps during those times when it doesn't update, it's possible the file is currently open.

Danick · Jul 19, 2019

theDBguy said:
Hi. I can't test this right now but try using your code while the spreadsheet is open just to verify if it will update or not. If it doesn't, then my guess is perhaps during those times when it doesn't update, it's possible the file is currently open.

Thanks - I'll look for that the next time. I managed to "sort of" duplicate the problem by having the spreadsheet opened before exporting. And as expected, the spreadsheet did not update. But I'm pretty sure Excel was closed when I was actually having the problem. Next time, I'll verify by going into the Windows Task Manager and checking that there aren't any excel processes hanging on.

theDBguy · Jul 19, 2019

Danick said:
Thanks - I'll look for that the next time. I managed to "sort of" duplicate the problem by having the spreadsheet opened before exporting. And as expected, the spreadsheet did not update. But I'm pretty sure Excel was closed when I was actually having the problem. Next time, I'll verify by going into the Windows Task Manager and checking that there aren't any excel processes hanging on.

Hi. Sounds like a plan. Good luck!

Danick · Jul 26, 2019

theDBguy said:
Hi. Sounds like a plan. Good luck!

Well it looks like it happened again and I made sure that Excel was definitely closed and no excel processors were running in the background for all users. This time, I tried a few things before resorting to a backup. What seems to have worked was to actually save the spreadsheet before closing. This is not normally done as the exported data to the spreadsheet is just a placeholder to preserve the master formats. The spreadsheet is usually saved as some other name if needed. But for some reason, in order to get the spreadsheet to accept new data, it needed to be saved at least once. Very strange that this does not happen all the time and just glitches like this every once in a while.

To further troubleshoot, I'm going to trying using

Code:

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, WbSheet, CurrentProject.Path & "\" & WbName, True

but to an .xls instead of .xlsx (just to see if it happens again) So far so good...

theDBguy · Jul 26, 2019

Danick said:
Well it looks like it happened again and I made sure that Excel was definitely closed and no excel processors were running in the background for all users. This time, I tried a few things before resorting to a backup. What seems to have worked was to actually save the spreadsheet before closing. This is not normally done as the exported data to the spreadsheet is just a placeholder to preserve the master formats. The spreadsheet is usually saved as some other name if needed. But for some reason, in order to get the spreadsheet to accept new data, it needed to be saved at least once. Very strange that this does not happen all the time and just glitches like this every once in a while.

To further troubleshoot, I'm going to trying using

Code:

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, WbSheet, CurrentProject.Path & "\" & WbName, True

but to an .xls instead of .xlsx (just to see if it happens again) So far so good...

Don't you just hate it when computers don't do what you tell them to do? I'm out of ideas at the moment.

transferspreadsheet sometimes has strange behavior (1 Viewer)

Danick

Registered User.

theDBguy

I’m here to help

Danick

Registered User.

theDBguy

I’m here to help

Danick

Registered User.

theDBguy

I’m here to help

Similar threads

Users who are viewing this thread