Importing an Access Query into Excel using CopyfromRecordset

You really are better off if you use a "Get External Data" connection from the Data tab to connect to your Access database and insert the query as a table (or PivotTable dependng on your intended use). This will be much faster than VBA and can be refreshed/reconnected at any time (even from VBA if you like).
Regards, Alistair

Do you know the VBA code off by hand as I prefer to do it that way.

Check out http://support.microsoft.com/kb/200190 for using ADO. You  will need to reference the ADO library in your VBA project, on mine PC it is called:
Microsoft ActiveX Data Objects 6.0 Library. Add it in the VBA IDE using Tools > References... You will then be able to browse the library in the object browser.
Basically you create a parameter object and associate with the command object before using the command object to buildyour recordset. If you only have 1 record you can just use FindFirst and grab the field you need.
Alistair

Will the CashResults workbook be open at the time? If so, and assuming it's not a parameter query (since you didn't say), you can use slightly simpler code:

Sub GetAccessData()
   ' Sample demonstrating how to return a recordset from an Access db
   ' late bound so does not require a reference to the Microsoft ActiveX Data Objects Library.
   
   Dim cnn As Object, strQuery As String, rst As Object
   Dim strPathToDB As String, strFormula As String, i As Long
   Dim wks As Worksheet
   
   
   ' output to activesheet
   Set wks = ActiveSheet
   
   ' Path to database
   strPathToDB = "g:\Data\Cash.mdb"
   
   Set cnn = CreateObject("ADODB.Connection")
   ' open connection to database
   With cnn
      .ConnectionTimeout = 500
      .Provider = "Microsoft.Jet.OLEDB.4.0"
      .ConnectionString = "Data Source=" & strPathToDB & ";"
      .Open
      .CommandTimeout = 500
   End With
   ' SQL query string - change to suit
   strQuery = "CashQuery"
   
   ' create new recordset
   Set rst = CreateObject("ADODB.Recordset")
   
   ' open recordset using query string and connection
   With rst
      .Open strQuery, cnn
      ' check for records returned
      If Not (.EOF And .BOF) Then
         'Populate field names
         For i = 1 To .Fields.Count
            wks.Cells(1, i) = .Fields(i - 1).Name
         Next i
         ' Copy data starting from A2
         wks.Cells(2, 1).CopyFromRecordset rst
      End If
      .Close
   End With

   ' clean up
   Set rst = Nothing
   cnn.Close
   Set cnn = Nothing
End Sub

Select allOpen in new window

That works...but as I said previously it produces precisely the same result as using Get External Data and importing the query as a table. And with a data connection updating the Excel copy of the data is then a simple connection refresh, to do it programmatically you need additional code to delete the out of date data and then re-call the import code. And processing record by record in VBA is SLOW. Even if you want to add further derived fields it would be much more scalable to import the basic table and then use a macro to put in place standard Excel formulas in adjacent columns to calculate the derived fields.
In short, whilst coding it using ADO in VBA works, I suggest it is not really best practice use of Excel, ADO or VBA? There are many situations where it makes sense to code directly off the database but this is not really one of them.
Regards, Alistair

Since it's the OP's choice, and we don't necessarily know the full situation, I don't think we can comment as to whether it's best practice or not. There's certainly nothing wrong with it, and CopyFromRecordset is not that slow really.

hI, its is a Parameter query. It asks which date you would like to import the data for.
In which case, if I linked the date to a Named Excel cell called "Daypath", what would be the code?

Cheers Justin

Hi guys, thanks! It works, but I now want to import a query into Excel & append the data to a worksheet.
How do I do that? I

That's what the code does, so I don't understand the question.

It does not APPEND in the Worksheet. I want to run 2 macros, 1 that imports to a fixed range in a worksheet & the other, which appends data to the bottom the previous macros' data on the same worksheet.The macros above only import to a fixed reference with Excel.

You just need to alter the range reference to something like:

wks.Cells(Rows.Count, "A").End(xlUp).Offset(1).CopyFromRecordset rst

Select allOpen in new window