Cannot expand named range.

[Dom]

I have an Access 2007 database which exports data to various Excel 2003
spreadsheets.

The code has been working successfully for about 2 years (it was previously
in Access 2003) until this week when I got the 3434 Error 'Cannot Expand
Named Range'. This only happens on 1 of the 10 Excel files.

Each Excel file is exactly the same - the only differences are the content
and quanitity of the data. The formats are the same.

I've tried deleting the Excel file and re-running the export for the problem
file, but I get the same error after the initial export.

When I delete the Named Range from the Excel File, the export works fine but
creates a new Tab for the data. When I run the code again, the same error
appears.

I have tried re-importing all the source data into Excel. Same error.

The file it's failing on has ~ 40,000 rows. I thought it may be this so I
tried exporting 5,000 rows but had the same error. Other files have >10,000
rows and they work fine so I don't think it's a size issue.

I am at a loss as to what may be causing this so any help, hints or clues
would be greatly appreciated.

Thanks

 

[Ken Snell [MVP]]

See if the information in this article shed some light on why you're seeing
this error:

Using the Range Argument of TransferSpreadsheet when Exporting Data to an
EXCEL File (VBA)
http://www.accessmvp.com/KDSnell/EXCEL_Export.htm#ExpRange