In VBA, how can one use the Tools --> Office Links --> Analyze it with Microsoft Office Excel

Question

I am attempting to export a query from MS Access into MS Excel in a way that does not carry over the Sorting and Grouping. I have noticed this functionality is simply available by opening a query and clicking:

Tools --> Office Links --> Analyze it with Microsoft Office Excel

However, I don't know how to get to this feature of MS Access programmatically using VBA.

I was going to try something like this:

 DoCmd.OpenQuery "QueryName", acViewNormal, acReadOnly
 DoCmd.AnalyseFeatureFunctionHere

or

 DoCmd.OpenQuery "QueryName", acViewNormal, acReadOnly
 SysCmd(acAnalyseFeatureFunctionHere)

or

 DoCmd.OpenQuery "QueryName", acViewNormal, acReadOnly
 Application.AnalyseFeatureFunctionHere

Answer 1

At worst you can use SendKeys to send the appropriate key presses... that might not be ideal though, you won't be able to (easily) tell when the operation is complete.

Answer 2

CommandBars("Menu Bar").Controls("Tools").Controls("Office Links").Controls("Analyze It With Microsoft Office Excel").accDoDefaultAction

Answer 3

It is possible to run menu commands with Run Command, for example to Output to Excel you could use:

DoCmd.RunCommand acCmdOutputToExcel

However, it would be more usual to use OutputTo or TransferSpreadsheet.