Access to Excel with VBA - Query the Recordset

[diddon]

I have the following code to extract the contents of a query in Access to Excel, which works fine. However I now need to query the data based on a variable that will be contained in the Excel file. I can't put the criteria into the access query, because several different templates need to run off the same query.

Any help would be great.

Thanks



Sub GetData()

Dim db As Database
Dim rs As Recordset

Set db = OpenDatabase("c:\MyDB.mdb")
Set rs = db.OpenRecordset("qryMyQuery")

With Worksheets("Data").[a2]
.CopyFromRecordset rs
End With

rs.Close
db.Close
Set rs = Nothing
Set db = Nothing

End Sub

 

[SkipVought]

hi,

need to query the data based on a variable that will be contained in the Excel file

Run this query FROM EXCEL automatically, when the workbook opens, via MS Query.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[diddon]

Obvious - got all caught up in the VBA and overlooked what's already in Excel. Many thanks.