Share results of Access Query?

[logius]

Is it possible to dump the results of an Access 2000 query into an Excel 2000 document? If you can show me an example of pulling a few fields from an Access table then dumping the query into cell "A1" of a new Excel document and the results into the rows/columns (basically mimick the table in Excel) into "B1" and on, I would really appreciate it.

Does this make any sense? Until 10 minutes ago, I didn't even know what a query was, so if clarification is required, please ask. ----------------------------------------
If you are reading this, then you have read too far...

lightwarrior@hotmail.com

 

[kevinclark]

Hi Logius

Hope this may help. With this example you will need to open an Excel Workbook and paste the code below into its Visual Basic Module. You will have to change the indicated variable to point to valid Paths and Database / Tables.
This will then pull the Data from the Database and paste it into the Excel Worksheet.

Sub Button1_Click()
'Select Microsoft DAO 3.6 Object Library
'from Tools .. References Menu

'Dimension your Variables
Dim dbSample As Database
Dim rsSample As Database
Dim r As Integer 'Row / Record Location
Dim WS As String 'WorkSheet Name
Dim tblSample as String 'Database Table Name
Dim strSQL As String 'Query / SQL
Dim dbPath As String 'Path to Database
Dim c As Integer 'Column / Field location

'Set the Variables
r = 0
'Change next 4 Variables to suit
WS = "Sheet1"
tblSample = "YourTableName"
strSQL = "Select * From" & tblSample
dbPath = "C:\My Documents\SampleDatabase.mdb"

'Open Database
Set dbSample = OpenDatabase(dbPath)
'Open Recordset
Set rsSample = dbSample.OpenRecordset(strSQL)

'Paste data to Excel WorkSheet
Do While rsSample.EOF = False
For c = 0 To rsSample.Fields.Count - 1
'Sheets(WS).Cells(Row,Column).value = rsSample.Fields(Index).value
Sheets(WS).Cells(r + 1, c + 1).Value = rsSample.Fields(c).Value
Next c
'Increment r to next Row / Record location
r = r + 1
'Move to next record
rsSample.MoveNext
Loop

End Sub