Export Queries to Named Excel Worksheets

billHassall

Registered User.
Local time
Today, 12:36
Joined
Feb 25, 2010
Messages
27
I am trying to export multiple queries into existing worksheets within the same spreadsheet, but instead of the export overwriting within the existing worksheets, it is creating additional worksheets with the same name plus a "1" suffix. I could rename the queries to the worksheet names, but would prefer to leave the query naming conventions and just specify the name of the worksheet that I want to export to

Any help would be appreciated, I am on Access 2010 and the code is as follows:-

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "12 Monthly Day 6 5R", MyPath & "\Day 6 Delivery.xls", False, "5 Region"

I want to export query "12 Monthly Day 6 5R"
into file "Day 6 Delivery.xls"
and overwrite worksheet "5 Region"

Bill
 
Code:
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "12 Monthly Day 6 5R", MyPath & "\Day 6 Delivery.xls", False, "[COLOR=Red]![/COLOR]5 Region"

Or

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "12 Monthly Day 6 5R", MyPath & "\Day 6 Delivery.xls", False, "[COLOR=Red]![[/COLOR]5 Region[COLOR=Red]][/COLOR]"
 
Thanks for the reply, I am now getting the following error

'$[5 Region]' is not a valid name. Make sure that it does not include invalid characters or punctuation and that it is not too long.

I did input ! and not $
If I try to export without naming the worksheet, the worksheet name is seperated by underscores, so I have tried to change to 5_region, I have renamed worksheet to 5 region and 5_region and tried all of the different combiunations, but still get the error

I tried without the square brackets and got the same message as above but without the square brackets

Bill
 
Can you upload the Workbook you're talking about? Don't include any sensitive data.
 
I deleted all of the existing worksheets in the spreadsheet, ran the code to allow Access to create the worksheets using the following format

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "12 Monthly Day 6 5R", MyPath & "\Day 6 Delivery.xls", False, "5 Region"

Formatted the sheets to how I wanted them and then when I subsequently run the code, it overwrites the existing sheets instead of creating new ones

Thanks for your help

Bill
 
Good job Bill!

It's the import option that requires the exclamation mark when defining the range.
 

Users who are viewing this thread

Back
Top Bottom