Extract the query in Excel

[Chateauk]

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]

Link Excel to Access query or export query to Excel.
Why involve Excel?

 

[Chateauk]

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

 

[Chateauk]

My team wants the button to generate an excel spreadsheet instead of them copying and pasting in excel

How do I update the vba code to export the query in excel?

 

[CJ_London]

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]

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]

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

 

[CJ_London]

Opening a query this way is extremely dangerous if the query produces an updateable recordset.

You can open it as a snapshot

 

[raziel3]

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.