views:

465

answers:

3

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
A: 

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.

jheriko
SendKeys won't work with Vista.
Remou
-1 Sendkeys is ALWAYS the wrong answer.
David-W-Fenton
+3  A: 

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

pk
This works. Thanks. Do you know if this is the preferred way of accessing Office Links? It seems like there would be some other exposed interface.
Curtis Inderwiesche
+2  A: 

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.

Remou
The only issue I ran into using TransferSpreadsheet is I didn't want the sorting and grouping structure to be exported into the Excel document. But using Office Links allowed the final product to be exported without this happening. This command also works very nicely.
Curtis Inderwiesche