Thread: Exporting a query from Excel to Access in a Excel macro

I have various actions I'd like running from my Excel macro in Access. I currently have this code:

Dim ac As Object
On Error Resume Next
Set ac = GetObject(, "Access.Application")
If ac Is Nothing Then
    Set ac = GetObject("", "Access.Application")
    ac.OpenCurrentDatabase "<access db>"
    ac.UserControl = True
End If
AppActivate "Microsoft Access"
ac.DoCmd.runmacro "<macro>"
ac.DoCmd.TransferSpreadsheet acExport ""
ac.Quit

As you can see this runs the "<macro>" but how do I add commands to export queries from Access to Excel and how can I open queries from Access?

Is it something like ac.DoCmd.openquery and ac.DoCmd.importexportspreadsheet (as per the commands we use in Access itself.

In addition I'd like to supress a few prompts I receive within Access:

- Prompt to modify data table: Yes
- Prompt to confirm you won't be able to undo changes this action query is about to make: Yes
- Prompt to confirm you are about to update xx rows: Yes

Is it possible to surpress/select Yes to all these prompts in Access with the Excel macro code above?

I've added a line to export a query to a Excel table:

ac.DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel14, "PM2 OMNIX Table", "spreadsheet1xlsx"

But I get the following error message:

'Could not find installable ISAM.'

I'm using Access 2010 and Excel 2010 is installed on the machine. I presume acSpreadsheetTypeExcel14 = Excel 2010? Or is there another way to export a query from Access now?