Strange Access to Excel export problem in Office 2010
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?
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?
ASKER
I don't have any named ranges in the code. I don't think there's anything in the select query or export selections that would limit the number of rows, but I will double-check. It is exporting the header row, maybe there's something different in the coding for the 2010 export process that I didn't allow for.
I was actually thinking it's something about the prep done for the second run...that it's either trying to fit into a box that's one row too big or is assuming something about having found a header record and then having problems because it's also trying to recreate the header, or if there's a delete rows command of some sort, it's picking one too few...
i.e., in the first: on round 1, you place 77 data rows + 1 header row. On round 2, it finds the header and STILL tries to include 77+1, leaving the existing +1
on the second: you place 77+1 header on round 1 and then round 2 clears only the 77 (count of data records), leaving one lone record behind (what would have accounted for that extra header row).
Not sure if that convoluted explanation makes sense anywhere but in my own brain!
-R
i.e., in the first: on round 1, you place 77 data rows + 1 header row. On round 2, it finds the header and STILL tries to include 77+1, leaving the existing +1
on the second: you place 77+1 header on round 1 and then round 2 clears only the 77 (count of data records), leaving one lone record behind (what would have accounted for that extra header row).
Not sure if that convoluted explanation makes sense anywhere but in my own brain!
-R
Remember, Excel internally stores what it thinks is the "End" of the data.
If this point is not updated, (by saving the file), then you may not get an accurate count of rows.
This means that in the Excel code, it may be doing something like this:
Cells.Find(What:="*", After:=[A1], SearchDirection:=xlPreviou
...which tries to find the last cell. If the last cell/Row is not accurate, then again, your row count may be off.
See here for more info:
http://www.ozgrid.com/VBA/ExcelRanges.htm
If this point is not updated, (by saving the file), then you may not get an accurate count of rows.
This means that in the Excel code, it may be doing something like this:
Cells.Find(What:="*", After:=[A1], SearchDirection:=xlPreviou
...which tries to find the last cell. If the last cell/Row is not accurate, then again, your row count may be off.
See here for more info:
http://www.ozgrid.com/VBA/ExcelRanges.htm
ASKER
All that's happening in the workbook is that Access is (supposedly) refreshing the data that has already been exported to Excel. There's no code at all in Excel itself.
Rem - sounds like your convoluted thinking might have a point. It makes some logical sense, but I'm not sure why that would be happening now when it never happened before. I changed absolutely nothing about the design of the Access database except to upgrade it to 2010. I'm thinking that there's some kind of behind-the-scenes logic that's changed in the export process. It's probably some supposedly improved code that makes it "easier" to export data from one program to another but is completely futzing up my simple little data update process.
Rem - sounds like your convoluted thinking might have a point. It makes some logical sense, but I'm not sure why that would be happening now when it never happened before. I changed absolutely nothing about the design of the Access database except to upgrade it to 2010. I'm thinking that there's some kind of behind-the-scenes logic that's changed in the export process. It's probably some supposedly improved code that makes it "easier" to export data from one program to another but is completely futzing up my simple little data update process.
ASKER
Does anyone know where all the export options went in Access 2010? When you exported to Excel from earlier versions, there used to be a bunch of options you could specify. I can't find anything like that in Access 2010.
In Access 2010:
Select the table to Export, then click:
External Data-->Export
Select the table to Export, then click:
External Data-->Export
ASKER
Yes, that I know how to do. But there is nothing there that allows me to specify whether to export the header row or not, etc. That's what I'm looking for. I would like to test it without exporting the header row to see if that makes any difference in the behavior. Do you know where that particular setting is in Excel 2010?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Then we seem to be drifting away from the original question...
I was helping you with the Export issue.
If your original issue is resolved, then assign points in this Q.
Then post a new question for this new "even weirder problem"
;-)
JeffCoachman
I was helping you with the Export issue.
If your original issue is resolved, then assign points in this Q.
Then post a new question for this new "even weirder problem"
;-)
JeffCoachman
ASKER
Your comment pointed me in the right direction; my comment explains the full solution that fixed my problem.
-Rachel