UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Closed TopicStart new topic
> Exporting Queries To Excel, I Want Worksheets To Be Overwritten, Not Created, Any Version    
 
   
AlbinoLion
post Dec 3 2019, 03:52 PM
Post#1



Posts: 7
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.

-Nathan
Go to the top of the page
 
theDBguy
post Dec 3 2019, 03:57 PM
Post#2


UA Moderator
Posts: 76,866
Joined: 19-June 07
From: SunnySandyEggo


Duplicate question. There's no need to post the same question twice. Good luck!

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    11th December 2019 - 09:30 AM