Cannot Expand Named Range

[Cathy]

I'm using Access 2007 on an Access 2003 formatted database.

I have recently had to add a column of information to some code that outputs
to excel. On a button, I have a first query that deletes the contents of a
table. Then next query refills the contents of the same table. Then I
export the table to a tab on a spreadsheet.

The table empties and refills properly. My problem is when moving the data
to excel. My original code looked for the field titles, and I have tried
ammending it not to look for field titles. Either way the code blanks out
the titles, refuses to move the data and locks up on the error "Cannot Expand
Named Range". I don't even use named ranges...

Here's my code:
' Runs Delete Query
DoCmd.OpenQuery "qryPCETotalEntries DELETE", acNormal, acEdit
' Runs Append Table Query
DoCmd.OpenQuery "qryPCETotalEntries APPEND", acNormal, acReadOnly

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9,
"PCETotalEntries", "C:\SopaReport.xls", False, ""

What can I do to correct this error?

Thank you in advance,
Cathy

 

[Douglas J. Steele]

Remove the , "" from the end of your TransferSpreadsheet statement.

 

[Christine Lawrie]

Cathy -


Not sure if you've found the solution, but I just had the same problem.

It's very simple - the file you are exporting to already exists. It seems the file is not over-written, the export tries to over-write the data in the file. If you have more data in your current export than was in the old one, it's the same as trying to copy and paste an Excel data range into a different sized range; you can't. Hence the "cannot expand named range" error.

All you have to do is delete or rename the old file, and you should be fine.