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

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Exporting Queries To Excel, I Want Worksheets To Be Overwritten, Not Created, Access 2016    
 
   
AlbinoLion
post Dec 3 2019, 03:54 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:55 PM
Post#2


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


Hi Nathan. Welcome to UtterAccess! welcome2UA.gif

Have you tried specifying the Range name for the export? Just a thought...

--------------------
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
 
AlbinoLion
post Dec 3 2019, 05:36 PM
Post#3



Posts: 7
Joined: 3-December 19



As far as DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "QueryName", "FilePath", True, "RangeName"?

I have, but ran in to the same issue
Go to the top of the page
 
WildBird
post Dec 3 2019, 07:05 PM
Post#4


UtterAccess VIP
Posts: 3,676
Joined: 19-August 03
From: Auckland, Little Australia


You will probably have to automate the Excel part. Make a connection, clear the sheet, and then may have to reset the pivots to look at what might be a different sized range.




--------------------
Beer, natures brain defragging tool.
Go to the top of the page
 
Debaser
post Dec 4 2019, 08:33 AM
Post#5



Posts: 156
Joined: 11-October 18



It's almost certainly going to be simpler to have Excel query Access and return the results to a Table (if necessary, you can also create Pivot tables directly from external queries). All you should need to do is then refresh the data in Excel and everything will just flow through.
Go to the top of the page
 
AlbinoLion
post Dec 4 2019, 12:17 PM
Post#6



Posts: 7
Joined: 3-December 19



I will look into connections, it looks like I can have them essentially refresh and pull the data. The queries are already set up to pull the date range so this might be easier. Thank you
Go to the top of the page
 
AlbinoLion
post Dec 4 2019, 12:26 PM
Post#7



Posts: 7
Joined: 3-December 19



Is there a way to have the connection be in a worksheet I previously created, I don't know an easy way to change the reference to all the calculations and pivot tables and I don't want to manually fix all of them if possible (there are tons of calculations/graphs)
Go to the top of the page
 
AlbinoLion
post Dec 4 2019, 12:39 PM
Post#8



Posts: 7
Joined: 3-December 19



I think figured out a way, it doesn't let me do it when the connection is created so I have to change the sheet via Existing Connections where it essentially creates a new copy. Than I can delete the original. It looks like it still refreshes properly. Let me know if there is an easier way iconfused.gif
Go to the top of the page
 
theDBguy
post Dec 4 2019, 12:47 PM
Post#9


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


Hi. Glad to hear you found a working solution. Good luck with your project.

--------------------
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
 
AlbinoLion
post Dec 4 2019, 01:34 PM
Post#10



Posts: 7
Joined: 3-December 19



Is it possible to create a connection that has a range of dates, which are inputted manually?
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    11th December 2019 - 10:00 AM