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

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/EXC...t.htm#ExpRange

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/


"Dom" <(E-Mail Removed)> wrote in message
news:A8D9A4C7-4C31-4985-86FB-(E-Mail Removed)...
>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
>