Access 2007 query export to Excel "feature"

[sensetech]

My client has an Access 2003 database with many queries, some of whose names starts with 200_. There is a VBA subroutine which loops through each QueryDef in the database and uses the TransferToSpreadsheet command to export the ones starting 200_ to an Excel workbook in Excel 2003 format where they end up on worksheets of the same name. It has all worked fine for several years.



We are testing a move up to Access 2007 and Excel 2007, making the relevant changes to the VBA code so it now produces an Excel 2007 .xlsx workbook. At first glance everything is again fine BUT then we notice that the resulting worksheet names have been prefixed by _, so query 200_Query1 appears as worksheet _200_Query1. This is not acceptable to the client!!!

I did some further tests and found that the "feature" seems to affect any query name starting with a number whereas those starting with a letter are unaffected. It also happens if the export is manual rather than via VBA automation. It doesn't happen with the SendObject command but that seems to be limited to 64k rows which defeats the main object of moving up to 2007.

Using Google I can't find any mention of this so does anyone on here know what's going on and if there's a fix?

[June7]

If the exports are all by VBA code and this doesn't happen with VBA procedure then why is this an issue?

[sensetech]

Sorry, I must have mislead you somehow. The problem happens if I do the export manually or if I use VBA TransferTo Spreadsheet. If I change the VBA to use SendObject then it's OK but limited to 64k rows.

Bottom line is that something has changed between Access/Excel 2003 and Access/Excel 2007 and I need to whether there's a fix (that doesn't involve renaming the queries!)

[June7]

The perils of upgrade!

I think options are:

1. change query names

2. more code to change names of the worksheets

[sensetech]

Yep, that's what I'd thought unless someone could tell me about a new option or whatever that I was missing in Access 2007. I'll persuade the client that the 'query rename' option is best as that feels cleaner than post-export code to rename the worksheets; there's already lots of weird code in there from the guy who put the app together initially.

As you have Access 2010, could you give it a try (manual export) to see what happens there? Any query whose name starts with a number should test it out.

[June7]

Yes, get the same result with Access 2010.

[sensetech]

OK, thanks.