Thread: Importing Access query into Excel:Microsoft Query

Do the dates connstatly change or are they open to whatever you want to pull through?

You could adapat the following code which uses SQL:

Sub DAOAccessToExcel_2()
Dim db As Database
Dim rs As Recordset
Dim intColIndex As Integer
Dim myDB As String

Dim FieldName As String
Dim MyCriteria As String
Dim TableName As String

'Change as Necessary for Query String
TableName = "tblEmployees"
FieldName = "Dept"
MyCriteria = "'Despatch'" 'WATCH QUOTES HERE!!!!

myDB = "C:\Documents and Settings\D11108\Desktop\Excel VBA Advanced\employees.mdb"
    Set TargetRange = Range("A1")
    Set db = OpenDatabase(myDB)
    Set rs = db.OpenRecordset("SELECT * FROM " & TableName _
                           & " WHERE " & FieldName & " = " _
                           & MyCriteria & ";", dbReadOnly)  ' filter records
    ' write field names
    For intColIndex = 0 To rs.Fields.Count - 1
        TargetRange.Offset(0, intColIndex).Value = rs.Fields(intColIndex).Name
    Next
    
    ' write recordset
    TargetRange.Offset(1, 0).CopyFromRecordset rs
    Set rs = Nothing
    db.Close
    Set db = Nothing
End Sub

If you're familiar with SQL it'll be easy but if not i can try and help

also you'd probably want to add a reference each one of the two dates so the Code pulls through the right Data.

oh yeah you'll aslo need to ad the reference Microsoft DAO 3.6 Object libarary for it to work