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?  
LVL 39
Hypercat (Deb)Asked:
Who is Participating?
Jeffrey CoachmanMIS LiasonCommented:
<But there is nothing there that allows me to specify whether to export the header row or not, etc.>
Then Export with code:
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12, "YourTable", "C:\YourFolder\YourFile.xlsx", True

The last argument here (True) specifies that the file should contain the header row.

You can see the VBA Help files on the TransferSpreadsheet Method for more info.
Are you using different named ranges in the code? Or do you have one select/import action on the export that places data with a header row and yet on the delete/clear, forgets to account for the extra row created by header addition?

Hypercat (Deb)Author Commented:
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.
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

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!
Jeffrey CoachmanMIS LiasonCommented:
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:=xlPrevious).Select
...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:
Hypercat (Deb)Author Commented:
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.
Hypercat (Deb)Author Commented:
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.
Jeffrey CoachmanMIS LiasonCommented:
In Access 2010:
Select the table to Export, then click:
External Data-->Export
Hypercat (Deb)Author Commented:
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?
Hypercat (Deb)Author Commented:
boaq2000, your post got me going in the right direction. I now have the "out of range" error fixed.  I converted the export macro I was using to VBA and when it converted it had the following code for each line:

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12, "MyTable", "C:\YourFolder\MyFile.xlsx", True, ""

I figured out that the "" at the end of the command was a Range statement. Eliminating that Range statement on the exports that were causing the error has fixed that problem. In the process, I had to also create a new spreadsheet.

However, now I'm having an even weirder problem.  Although all of the data is exporting correctly, none of the links on the report spreadsheets to the exported data cells are updating when I open the workbook. The only way I've found so far to force the links to update with the new data is to re-link them or manually re-enter the cell references, which is nuts!

I'm at a loss as to why these links aren't updating properly.  Each cell has a simple link to a cell on another worksheet containing data, like so: =Bal_Sheet!E2 [Bal_Sheet being the name of the worksheet where the exported data has been refreshed.]

Any ideas?

Jeffrey CoachmanMIS LiasonCommented:
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"


Hypercat (Deb)Author Commented:
Your comment pointed me in the right direction; my comment explains the full solution that fixed my problem.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.