1. In Access, create a table of sample data: enter the following data in a new table:the accidental tourist,12/1/2009,$6.01
the accidental tourist,12/3/2009,$7.98
iron john,12/5/2009,$4.98
iron john,12/6/2009,$5.98
2. Double-click the column headers (e.g. 'Field1') and replace each with these headers, in this order:book,datesold,netsaleSave the table ('control-s') with the name 'books.'
3. Create a query from the table, and press the 'Esc' key in the 'show table' dialog box. Right-click on the query's tab and select 'SQL view.' Enter the following in the code window:SELECT books.* INTO queryresults
FROM books
WHERE (((books.book) Like '*acc*'));Save the query ('control-s') and name it 'vbaquery.'
4. Open Excel and press the toolbar's 'Data>From Access' icon. Select the 'queryresults' table in the 'Select Table' dialog box. Click 'OK' on the 'Import Data' dialog box and notice the query's results: only the 'iron john' books are shown. Save the Excel file with any name, and close it.
5. Reopen the 'books' database in Access. Open the 'vbaquery' and revise its 'Criteria:' field to read 'Like '*acc*'' (Don't type the double quotes. Do type the inner, single quotes.) Resave the query.
6. Create a new query. Type the following SQL statement in the 'SQL view' window, then save the query as 'dropqueryresults':DROP TABLE queryresults;
7. Enter the Visual Basic integrated development environment (IDE) by pressing 'alt-f11,' then select 'Insert>Module.' Paste the following code into the new module's blank code window:Public Sub runquery()
'delete the results table first
On Error GoTo DO_QUERY
RunQueryForExcel ('dropqueryresults')DO_QUERY:
RunQueryForExcel ('vbaquery')
End SubPublic Sub RunQueryForExcel(qName As String)
DoCmd.SetWarnings False
CurrentDb.Execute qName
DoCmd.SetWarnings True
End Sub
8. Position the cursor anywhere in the 'runquery' subroutine and press 'F5' to run the query. Reopen the Excel workbook you previously opened and notice the updated data: your macro has replaced the 'iron john' rows with the 'accidental tourist' rows. (Access 2007 or later users can stop here.)
9. (For users of Access 2003 and earlier). Use step 7 to paste the following code into a new module in the Visual Basic IDE:
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Public Sub pasteToExcel()Const qName = 'vbaquery'
Dim db As DAO.Database
Dim recset As DAO.Recordset
Dim s As String
Dim appXL As Excel.Application
Dim ro, co'''''''''''''''''''''''''
Set appXL = CreateObject('Excel.Application')
appXL.Workbooks.AddSet db = CurrentDb
Set recset = db.OpenRecordset(qName)
s = 'book' ', ' 'dateddsold' ', ' 'netsale' vbCr
appXL.ActiveSheet.Cells(1, 1) = s
ro = 2
co = 1
s = ''
Do While Not recset.EOF
s = s recset![book] ', ' recset![datesold] ', ' recset![netsale] vbCr
appXL.ActiveSheet.Cells(ro, co) = s
recset.MoveNext
ro = ro 1
s = ''
Loop
recset.Close
db.Close
appXL.ActiveWorkbook.SaveAs ('c:\dataFromAccess.xls')
appXL.QuitEnd Sub
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Select 'Tools>References' and check the 'Microsoft Excel Objects Library.'
10. Return to Access and do steps 1 to 3. However, for step 3, paste this SQL code into the SQL code window:SELECT books.*
FROM books
WHERE (((books.book) Like '*acc*'));
11. Return to the Visual Basic IDE. Place the cursor inside the 'pasteToExcel' function and press 'F5' to run the function. Open the Excel file 'c:\dataFromAccess.xls' to view the results.
Read more ►