Extract the query in Excel (1 Viewer)

Chateauk

New member
Local time
Today, 08:31
Joined
Nov 20, 2023
Messages
7
I've created a button on a form that calls this procedure. How do I update the procedure to open the query in Excel xlsx instead of MS Access datasheet view?

Private Sub RunMonthlyReportBtn_Click()

DoCmd.OpenQuery "qryMonthlyReport", acViewNormal, acReadOnly


End Sub
 

June7

AWF VIP
Local time
Today, 05:31
Joined
Mar 9, 2014
Messages
5,375
Link Excel to Access query or export query to Excel.
Why involve Excel?
 

Chateauk

New member
Local time
Today, 08:31
Joined
Nov 20, 2023
Messages
7
Link Excel to Access query or export query to Excel.
Why involve Excel?
My team wants the button to generate an excel spreadsheet instead of them copying and pasting in excel
 

CJ_London

Super Moderator
Staff member
Local time
Today, 14:31
Joined
Feb 19, 2013
Messages
16,485
Investigate using the transferspreadsheet function to transfer the query to excel. Then either open excel or link to it to see the result
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 09:31
Joined
Feb 19, 2002
Messages
42,724
Opening a query this way is extremely dangerous if the query produces an updateable recordset. You have no way to prevent users from changing or deleting large numbers of records and there is no recovery from this.

Exporting to Excel makes sense if the user wants to use the data for other reporting but having the Access app open the spreadsheet is rare. If the user wants to just view the data, a better solution would have been a form based on the query. If the user wants to update the spreadsheet or format it as a report, etc, then you have to automate Excel to open the workbook and now the user is out of your application's control so hopefully, he is not expecting any changes in the data to be reflected in the access application.
 

Chateauk

New member
Local time
Today, 08:31
Joined
Nov 20, 2023
Messages
7
Opening a query this way is extremely dangerous if the query produces an updateable recordset. You have no way to prevent users from changing or deleting large numbers of records and there is no recovery from this.

Exporting to Excel makes sense if the user wants to use the data for other reporting but having the Access app open the spreadsheet is rare. If the user wants to just view the data, a better solution would have been a form based on the query. If the user wants to update the spreadsheet or format it as a report, etc, then you have to automate Excel to open the workbook and now the user is out of your application's control so hopefully, he is not expecting any changes in the data to be reflected in the access application.
I do agree with your approach. Thank you
 

raziel3

Registered User.
Local time
Today, 10:31
Joined
Oct 5, 2017
Messages
262
Power query that sh##.

Just make sure your query and its dependants don't have the Nz function anywhere.


https://learn.microsoft.com/en-us/power-query/connectors/access-database

and the great thing about a data-connected spreadsheet is that it's dynamic. As soon as the data is updated in the database you just refresh the spreadsheet and you get all the updated data.
 
Last edited:

Users who are viewing this thread

Top Bottom