Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Strange Access to Excel export problem in Office 2010

Posted on 2011-03-01
12
Medium Priority
?
796 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)
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
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
 
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 1000 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

Does Your Cloud Backup Use Blockchain Technology?

Blockchain technology has already revolutionized finance thanks to Bitcoin. Now it's disrupting other areas, including the realm of data protection. Learn how blockchain is now being used to authenticate backup files and keep them safe from hackers.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

AutoNumbers should increment automatically, without duplicates.  But sometimes something goes wrong, and the next AutoNumber value is a duplicate.  This article shows how to recover from this problem.
Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

721 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