Solved

Strange Access to Excel export problem in Office 2010

Posted on 2011-03-01
12
784 Views
Last Modified: 2013-11-27
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?  
0
Comment
Question by:Hypercat (Deb)
  • 6
  • 4
  • 2
12 Comments
 
LVL 7

Expert Comment

by:RemRemRem
ID: 35010364
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?

-Rachel
0
 
LVL 38

Author Comment

by:Hypercat (Deb)
ID: 35010414
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.
0
 
LVL 7

Expert Comment

by:RemRemRem
ID: 35010546
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
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 35011637
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:
http://www.ozgrid.com/VBA/ExcelRanges.htm
0
 
LVL 38

Author Comment

by:Hypercat (Deb)
ID: 35012258
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.
0
 
LVL 38

Author Comment

by:Hypercat (Deb)
ID: 35012291
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.
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 35017079
In Access 2010:
Select the table to Export, then click:
External Data-->Export
0
 
LVL 38

Author Comment

by:Hypercat (Deb)
ID: 35018182
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?
0
 
LVL 74

Accepted Solution

by:
Jeffrey Coachman earned 250 total points
ID: 35018438
<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.
0
 
LVL 38

Assisted Solution

by:Hypercat (Deb)
Hypercat (Deb) earned 0 total points
ID: 35019788
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?


0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 35020373
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
0
 
LVL 38

Author Closing Comment

by:Hypercat (Deb)
ID: 35187405
Your comment pointed me in the right direction; my comment explains the full solution that fixed my problem.
0

Featured Post

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now