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?