Export access query results to an Excel worksheet

[jmoon]

I have eight seperate Access queries and want to export the results of each to a seperate worksheet tab in the same Excel workbook.

I have tried the macro funtion 'OutputTo' but this seems to export the results to a named workbook (I can't specify a worksheet), and overwrite the file after each query is run.

Please help? ray:

 

[Gates Is Antichrist]

I am not an expert in this area but will share what I find to be useful.

If manual operation is acceptable, you can do as I do for all queries - on its output view, Ctrl-A, and in Excel go Ctrl-V. The pasting can take a while for truly huge amounts of data, but the fact is, simple copy- and pasting really will handle truly huge amounts of data.

If you don't have that luxury, consider working from the other way (in Excel). You can OPEN an .MDB file (the commond open dialog includes a category for it), and it sets up a query to the .MDB ... something like

Provider=Microsoft.Jet.OLEDB.4.0;Password="";User ID=Admin;Data Source=C:\MYDIR\MYDATABASE.MDB;Mode=ReadWrite;Extended Properties="";Jet OLEDB:System database="";Jet OLEDB:Registry Path="";Jet OLEDB:Database Password="";Jet OLEDB:Engine Type=5;Jet OLEDB:Database Locking Mode=1;Jet OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:Global Bulk Transactions=1;Jet OLEDB:New Database Password="";Jet OLEDB:Create System Database=False;Jet OLEDB:Encrypt Database=False;Jet OLEDB:Don't Copy Locale on Compact=False;Jet OLEDB:Compact Without Replica Repair=False;Jet OLEDB:SFP=False

The process is achieved by
Workbooks.OpenDatabase Filename:="C:\MYDIR\MYDATABASE.MDB", _
CommandText:=Array("MyQuery"), CommandType:=xlCmdTable

But don't listen to me LOLOL. Have your searched the Excel side of this board for this answer?

 

[jmoon]

Cheers for the advice, but manual cut+paste would be a last resort.

I've tried to connect to the mdb file in Excel but it appears to link only to tables. I can't work out how to create a link to execute the queries in Access.