Output from Access 97 to Excel

What's the name of the sheet that gets renamed?

It is Sheet1.

Can I see the workbook BEFORE it's "repaired"?

Sure. I just emailed an example to you. I have several others created from Access 97 in the same way but they have client confidentail data.

Okay. What's the name of the table/query/report you're exporting from?

Have you tried Transfer spreadsheet method instead?

I happened to be on the phone with MrExcel, so I asked him about it. He says it sounds familiar and we came up with these questions for you.

I've also put a pointer from the Access forum to this Q to see if any Access geeks can help us. I've been troubleshooting MS Office for years and never heard of this.

The sample report is called "UpdateReportOutAll_VariationforExport_Other" but the behaviour is the same with any other report. The reports to be exported already exist with the data required and are quite sophisticated, for instance some code behind the report that fills certain fields, so it would require some effort to switch to building a table that can then be exported using transfer spreadsheet. But I'll "dummy up" a quick transfer spreadsheet test to see if that works.

I did some more experimenting. One test was that I opened the generated report in Excel 2000 and then simply saved it. When prompted on the save "workbook is a microsoft Excel 5.0/95 workbook. Do you want to overwrite it with latest format?" I replied No to save it in current format (presumably still Excel 5.0/95). However, I'm then able to open in Excel 2002 with no problems! So the mere act of opening in Excel 2000 and saving without, presumably, changing the format has an effect. Interesting!!
David

There's your problem. Likely, Excel 2000 says "the hell with it" and just names it Sheet1.

XP and above are reporting the error to you. Rename the export to something LESS than 32 characters...

That sounded likely but unfortunately doesn't work. Even using a different report name (e.g.: URTest) I get the problem. When I open the workbook in Excel 2000 the sheet is still called Sheet1. Seems like the report output from Access does not name the sheet based on the report name. This is different from when a query is exported to Excel when the query name is used as the sheet name. If I open my URTest in Excel 2002 I get the same invalid sheet name error.
David

Okay. Don't think I'd be able to help without the DB...