Strange Access to Excel export problem in Office 2010
Posted on 2011-03-01
I have an Access database that I've been using for years to extract data from a SQL database and then export it to an Excel workbook. The Excel workbook consists of several spreadsheets that are automatically updated with new data every time the Access export is run. Then there are a couple of other spreadsheets that link to that data and use it to create custom financial statements. It's all pretty simple really.
This database has already gone through one version upgrade, from Office 2000 to 2003. The problem began with the upgrade to Office 2010 a couple of weeks ago. I believe an account was added a few weeks ago as well, so it could be coincidental that the 2010 upgrade was done at the same time.
Here's the crux of the problem: The export macro works fine if the spreadsheets it is trying to update are blank. However, once you run the export macro one time, the next time you run the same macro, it fails with an error message. IIRC the error message says "Unable to expand range" or something like that. If I look at the workbook after the failure, the spreadsheet on which it fails is completely blank EXCEPT for the last line. So, for some reason it's failing to delete/update the last line of data and that's what is making it fail.
This happens only on both of the longer spreadsheets that are included in the export. By "longer" I mean they are each exactly 78 lines long, which to me is not a very large spreadsheet at all. However, if I go into the spreadsheets and delete the last line (ONLY the last line) on both of them everything works fine. So it could be a matter of the number of lines of data, although there's nothing in the Access macro as far as I can tell that would limit the number of lines that can be exported/updated.
This a process that is normally run by the assistant administrator of the firm who is not that savvy and I don't want to have to tell her how to go into the spreadsheet and delete the old data before she runs the Access program.
Anyone have any ideas what might be causing this?