"Cannot expand named range"

J

JH

I have a process that I support that has started generating an error.

This process imports a spreadsheet from Excel into Access. It then runs
queries to split the data into individual accounts and then it exports the
split up data back to Excel using the following command:

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel3, "Temp55a",
strLoc, True

83 Spreadsheets of 86 get created.
The 84th spreadsheet fails with error number 3434 - Cannot expand named range.
Once the error is encountered the database closes with the process unfinished.

I am completely baffled by this. There is NO named range in the command.

The parameters break down to action type (acExport), Excel version
(acSpreadsheetTypeExcel3), Access table name (Temp55a), File Name (strLoc),
Column headings (True).

I have searched extensively trying to find an answer to this issue with no
luck. In fact, I have not even seen anyone else having a similar issue.

Any help would be greatly appreciated.
 
S

Smallweed

Just a thought: there isn't a blank row in the data being exported is there?
Excel may be creating a hidden name for the imported data that isn't being
addressed properly (as you probably know, not being a database package -
despite being used far too much as one! - Excel cannot understand lists with
blank rows).
 
J

JH

No. There are no blank rows in the Access table that is being exported.

Any other ideas?
 
K

Klatuu

acSpreadsheetTypeExcel3 is way out of date. I don't know what version of
Excel you are using, but since 2000, it has been acSpreadsheetTypeExcel9. I
have heard of other problems using the same code. I would suggest you either
align your code to reference the version of Excel you are using or leave it
blank at 9 will be used by Default.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top