Vba – Set focus on any object

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.

I would use DoCmd.TransferSpreadsheet ... except you said you want the exact same formatting as you get from "Analyze It with Microsoft Office Excel"

To duplicate the formatting, you can use:

DoCmd.RunCommand acCmdOutputToExcel

However, that creates a spreadsheet from the active object, and if run from a command button on a form, the form will be the active object ... so you would create a spreadsheet of the form ... which is not what you want.

Since your goal is to create a spreadsheet of a query, add a text box (txtQueryName) for the user to indicate which query to export.

Then your command button click event could be something like this:

Private Sub cmdAnalyzeWithExcel_Click()
    DoCmd.OpenQuery Me.txtQueryName
    DoCmd.RunCommand acCmdOutputToExcel
    DoCmd.Close acQuery, Me.txtQueryName
End Sub

That is crude, but it works for me with Access 2003. I think a better approach would be to load a combo or list box control with the available query names and let the user make her selection from there. You're on your own for that.

If it were me, I wouldn't try to do this from a command button. I think a custom menu or ribbon item would be more appropriate. Or it could be simpler to create an AutoKeys macro which assigns a keyboard shortcut to run a procedure (or call another macro) which does DoCmd.RunCommand acCmdOutputToExcel If AutoKeys sounds good to you, see Run an Access macro by using a keyboard shortcut