Search code examples
ms-accessvbaexport-to-excel

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

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
Solution

  • 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.