X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
> Exporting Queries To Excel, I Want Worksheets To Be Overwritten, Not Created, Any Version    
post Dec 3 2019, 03:52 PM

Posts: 9
Joined: 3-December 19

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.

Go to the top of the page

Posts in this topic

Custom Search

RSSSearch   Top   Lo-Fi    20th February 2020 - 02:19 AM