MS Access Export Query To Excel - Space In Query Name Causing Issues

[Jeffin]

Hello,

I am using the DoCmd.TransferSpreadsheet method to export some queries into an excel workbook.

My issue is simple but I can't seem find a valid solution online, other than renaming my queries.
Anyways, the queries I'm trying export have spaces in them, and the end user wants the worksheets to be named exactly the same as the queries, with the spaces; however, this is not translating correctly to the worksheet names.

Example:

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "11 - Rates - Ocean", _
Application.CurrentProject.Path & "\IL Rate Workbook.xlsx", True, "11 - Rates - Ocean"

The query "11 - Rates - Ocean" is resulting in a worksheet name of "_11___Rates___Ocean".

I understand that the spaces are causing all the underscores, but why is there a spontaneous underscore in front of the 11 too? Anyways, are there any possible solutions for this issue besides renaming all my queries???

Note: I've also tried putting single quotations in the double quotations like the following: "'11 - Rates - Ocean'".

Thank you for all the comments and advice in advanced.

Best Regards,

- Jeffin

[jonh]

Using underscores is a common way of avoiding problems if something doesn't like object names that contain spaces or start with numbers.

Use OutputTo instead.


DoCmd.OutputTo acOutputQuery, "11 - Rates - Ocean", acFormatXLSX, CurrentProject.Path & "\IL Rate Workbook.xlsx"

[Jeffin]

Using underscores is a common way of avoiding problems if something doesn't like object names that contain spaces or start with numbers.

Use OutputTo instead.


DoCmd.OutputTo acOutputQuery, "11 - Rates - Ocean", acFormatXLSX, CurrentProject.Path & "\IL Rate Workbook.xlsx"

Thank you for your reply, this method perfectly maintains the format and name of the query but unfortunately it's limited to one query, each time you use this method the previous query/worksheet get overwritten by the next query/worksheet.

"DoCmd.TransferSpreadsheet" allows me to export all 11 queries to the same workbook as 11 separate worksheets, but it's draw back is the name and format.

Are there any workarounds for the "DoCmb.OutputTo" method so that I can add multiple queries/worksheets to one workbook?

Thank you,

- Jeffin

[jonh]

Can't you just add data connections in Excel and refresh them?

[Jeffin]

Thanks Jonh, that works great!

Best Regards,

- Jeffin