How to Import Access Query Into Excel

1. Open Access and create the following table of sample data, which has field names in the top row. Click 'Create>Table' to create the table.game,saledate,totalsale
quake,12/1/2009,$6.01
quake,12/3/2009,$7.98
guitar hero,12/5/2009,$4.98
guitar hero,12/6/2009,$5.98Save the table (by pressing 'control-s') with the name 'games.'
2. Create a new query for the games table by clicking 'Create>Query Design,' right-clicking on the 'Query1' text in the query's tab, and clicking 'SQL View.'Enter the following statement in the SQL code window:SELECT games.* INTO myqueryres
FROM games
WHERE (((games.game) Like '*tar*'));Save the query (by pressnig 'control-s') as 'myquery.'
3. Run the query by double-clicking 'myquery' in the navigation pane, then close Access and open Microsoft Excel.
4. Click 'Data' and choose 'From Access.' In the 'Select Data Source' dialog box, open the Access database you created the games table in. Select the table 'myqueryres' from the 'Select table' dialog box, then press 'OK' on the 'Import Data' dialog box. Notice that Excel has imported the query.
5. Close Excel, remembering the filename when you save the file. Re-open Access. Revise 'myquery' in 'SQL View' to read as follows:SELECT games.* INTO myqueryres
FROM games
WHERE (((games.game) Like '*ua*'));
6. Save and re-run the query, then close Access and re-open the Excel file you created the table link in. Select 'Data' and choose 'Connections,' then press the 'Refresh' button in the 'Workbook Connections' dialog box. Close the dialog box and notice that the query results have changed.
7. (For users of Excel 2003) Complete steps 1 and 2. Run 'myquery,' then press 'alt' 'F11' to enter the Visual Basic integrated development environment (IDE). Press 'Insert' and select 'Module,' then paste the following program code into the new code window:Public Sub sendToExcel()'''''''''''''''''''''''''
Set curdb = CurrentDb
Set recs = curdb.OpenRecordset('myqueryres')
st = 'game' ', ' 'saledate' ', ' 'totalsale' vbCrSet xlapp = CreateObject('Excel.Application')
xlapp.Workbooks.Add
r = 1: c = 1
xlapp.ActiveSheet.Cells(r, c) = st
r = 2
st = ''
Do While Not recs.EOF
st = st recs![game] ', ' _
recs![saledate] ', ' recs![totalsale] vbCr
xlapp.ActiveSheet.Cells(r, c) = st
recs.MoveNext
r = r 1
st = ''
Loop
recs.Close: curdb.Close
xlapp.ActiveWorkbook.SaveAs ('c:\accessquery.xls')
xlapp.QuitEnd Sub
8. Click 'Tools' and choose 'References,' then check the check box labeled 'Microsoft Excel Objects' so your macro can recognize the functions that Excel makes available.
9. Run your macro by placing the cursor anywhere in the sendToExcel subroutine and pressing 'F5.'
10. Double-click the file 'c:\accessquery.xls' from Windows Explorer, and notice the query's results in Microsoft Excel when it opens.