Cannot Expand Named Range

[22strider]

Hello Friends

Has anybody figured out why some times we get following error message when exporting data to Excel?
"Cannot Expand Named Range"
Use Access 2007 and I run a weekly export and have saved export that I use. Earlier I used to export query result in Excel 2003 and never had this problem. But with time number of records have grown and exporting to Excel 2003 is not an option anymore. So I tried exporting to Excel 2007. That's when I started getting this problem. And it only happens when export is over writing a file that already exists in the destination folder. That is critical if you are looking to automate the function.
Does anyone know fix for it? Or is it unfixable so that I should move on.

Thanks
Rajesh

 

[shg]

Have you saved the workbook as a native xlsx/m file? If not, you're in compatibility mode, which means you have the same 64K lines you had before.

 

[22strider]

What is xlsx/m?
When I open the exported file (with excel 2007) I don't see "compatibility mode" above the menu bar I see when I open 2003 files with Excel 2007

 

[shg]

xlsx and xlsm (and xlsb) are the native formats for Excel 2007+ files.

How is the name defined?

 

[22strider]

Name of the file that I am exporting is "CostReport" and its type is "Microsoft Office Excel Worksheet". In my other codes (vba) I am refering this file as CostReport.xlsx
But this export is throwing me off because it won't over write.

 

[Phildaburn]

What if you delete the old file first with vba?

Kill "C:\MyFiles\CostReport.XLSX"

Phil...

 

[22strider]

Thanks Phil; exporting by deleting file first works just fine. Sometimes easy solutions are hard to get to.

Thanks again
Rajesh

 

[Phildaburn]

My pleasure. Glad I could help.

Phil...