delali said:
this is an excel/access question. i was wondering how to
make excel run a querry in access and put the result of
the querry into a spreadsheet.
Hi Delali,
I am not an Excel expert.......
this is what I think I know....
I created a new Excel workbook on C:\
C:\book1.xls
I added a command button to Sheet1.
Then tried the following code from KB 295646:
Private Sub CommandButton1_Click()
'adapted from
http://support.microsoft.com/?kbid=295646
'HOWTO: Transfer Data from ADO Data Source to Excel with ADO
On Error GoTo Error_CommandButton1_Click
Dim sNWind As String
Dim cnSrc As New ADODB.Connection
Dim strSQL As String
sNWind = "C:\Program Files\Microsoft Office\Office\Samples\Northwind.mdb"
cnSrc.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & sNWind & ";"
cnSrc.CursorLocation = adUseClient
'-----------------
'How to Copy
' (destination sheet (no "$") must not yet exist,
' creates column headings in first row of dest sheet)
'----------------
'strSQL = "SELECT * INTO [Excel 8.0;Database=" & _
ThisWorksheet.Path & _
"\book1.xls].[Sheet2] FROM Customers"
'Debug.Print strSQL
'cnSrc.Execute strSQL
'MsgBox "Have successfully filled new Sheet2 with Customers table."
'--------------------
'How to Append
' (Both the destination workbook and worksheet must exist,
' column headings must already be present)
'-------------------
strSQL = "INSERT INTO [Sheet3$] IN '' [Excel 8.0;Database=" & _
ThisWorkbook.Path & _
"\book1.xls] SELECT * FROM Customers"
Debug.Print strSQL
cnSrc.Execute strSQL
MsgBox "Have successfully appended Customers table to Sheet3."
cnSrc.Close
Exit_CommandButton1_Click:
If Not (cnSrc Is Nothing) Then
Set cnSrc = Nothing
End If
Exit Sub
Error_CommandButton1_Click:
MsgBox "Error " & Err.Number & " = " & Err.Description
Debug.Print "Error " & Err.Number & " = " & Err.Description
Resume Exit_CommandButton1_Click
End Sub
The "append" portion worked great after adding Sheet3
and filling first row with column names (field names from
table Customer).
I could not get the "copy" portion of code to work
"within the xls." But it would work "outside the xls."
I created a new "book1.xls" in Office dir
C:\PROGRAM FILES\MICROSOFT OFFICE\OFFICE\book1.xls
(with no "Sheet2")
then changed code to
strSQL = "SELECT * INTO [Excel 8.0;Database=" & _
Application.Path & _
"\book1.xls].[Sheet2] FROM Customers"
cnSrc.Execute strSQL
and this succeeded.
I don't know why the "copy" routine would not work
"within" the xls.
Good luck,
Gary Walter
Note: In code window, I had to set reference to
Microsoft ActiveX Data Objects 2.7 Library
