I have an Access query that I want to export regularly to a specific sheet in Excel. This sheet has a chart that is dependent upon the cells' data.
I am using the following command:
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "Query Name", "C:\test\test.xlsx", True, "Sheet with Chart"
However, it exports to Sheet "_Sheet with Chart".
It always creates a new sheet, named with an underscore immediately preceding whatever sheet name I choose. In some instances, it will put a '1' immediately after a sheet name - for example, if I use "Sheet1" in the command, the resulting action is a new sheet with "Sheet11" showing the newly exported data.
Is there a way to have it export to an existing sheet that I name? Is the command different? Is there a setting in Excel that needs to be changed? Or is this not even possible? Any help is appreciated.