Printable Version of Topic

Click here to view this topic in its original format

UtterAccess Forums _ Microsoft Excel _ Exporting Queries To Excel, I Want Worksheets To Be Overwritten, Not Created

Posted by: AlbinoLion Dec 3 2019, 03:52 PM

I have created a button that will allow users to export data from Access to Excel for different date settings (month, week, etc). The data populates the appropriate worksheet and the data is referenced/used in various calculations and pivot tables on a separate worksheet within the workbook. Initially I wasn't having this issue, but have been running into it as I have tried to create new workbooks. When I export the files instead of overwriting the data that is already there it creates a new worksheet (IE QueryName1 instead of QueryName); this is an issue as the calculations and pivot tables don't reference the exported data if a new worksheet is created. This is also the reason I can't use a "Kill" as the references would be deleted.

Ideally users could just export the data and refresh the workbook in order to view/analyze the data for each date setting

I am currently using the following code:

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "QueryName", "FilePath", True

Any help would be appreciated, thank you.


Posted by: theDBguy Dec 3 2019, 03:57 PM There's no need to post the same question twice. Good luck!