Export access query to formatted Excel spreadsheet - VBA.

%3CLINGO-SUB%20id%3D%22lingo-sub-2255674%22%20slang%3D%22en-US%22%3EExport%20access%20query%20to%20formatted%20Excel%20spreadsheet%20-%20VBA.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2255674%22%20slang%3D%22en-US%22%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20a%20query%20(qry_myexport)%20and%20if%20I%20right%20click%20it%20in%20the%20navigation%20pane%20the%20option%20to%20export%20it%20(formatted)%20to%20excel%20is%20shown.%3CBR%20%2F%3E%3CBR%20%2F%3E%3C%2FP%3E%3CP%3EHow%20can%20I%20code%20a%20button%20to%20do%20this%20in%20VBA%3F%3C%2FP%3E%3CP%3E%3CBR%20%2F%3EI%20have%20searched%20extensively%20and%20all%20the%20VBA%20codes%20I%E2%80%99ve%20found%20are%20quite%20complex.%26nbsp%3B%20So%20I%20have%20simply%20been%20Right%20Clicking%20the%20query%20and%20exporting%20it%20that%20way.%26nbsp%3B%20But%20surely%20there%20must%20be%20a%20simple%20way%20to%20do%20this%20with%20VBA.%3C%2FP%3E%3CP%3E%3CBR%20%2F%3EThank%20you%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2255674%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EAccess%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2255863%22%20slang%3D%22en-US%22%3ERe%3A%20Export%20access%20query%20to%20formatted%20Excel%20spreadsheet%20-%20VBA.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2255863%22%20slang%3D%22en-US%22%3EHave%20you%20looked%20into%20the%20DoCmd.TransferSpreadsheet%20code%3F%3CBR%20%2F%3ESee%20%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Foffice%2Fvba%2Fapi%2Faccess.docmd.transferspreadsheet%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3Ehttps%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Foffice%2Fvba%2Fapi%2Faccess.docmd.transferspreadsheet%3C%2FA%3E%3C%2FLINGO-BODY%3E
Occasional Contributor

 

I have a query (qry_myexport) and if I right click it in the navigation pane the option to export it (formatted) to excel is shown.

How can I code a button to do this in VBA?


I have searched extensively and all the VBA codes I’ve found are quite complex.  So I have simply been Right Clicking the query and exporting it that way.  But surely there must be a simple way to do this with VBA.


Thank you

2 Replies
I worked it out
I just did the right click export, then saved the export with a name “export_to_excel”
Then added a button with this
DoCmd.RunSavedImportExport " export_to_excel "