First, you'll need to add a reference to the latest Microsoft DAO or ADO
library, which allows you to communicate between Excel and Access data
objects. ADO is now the tool of choice, but I use DAO in this example
for simplicity.
Basically, what you need to do is define a Database object (your Access
database) and a parameter query (created in Access). Then you fill the
needed parameters from your Excel data and run the query.
Dim db As Database
Dim strDbName As String, strQryName as String
Dim strParam as String
Dim x as Integer
Dim qry As QueryDef
Dim rst as Recordset
Dim rngCopySpot as Range
1) First open a connection to your database and define your query:
strDbName = "E:\Projects\My Data.mdb"
strQryName = "qryGetExcelParams"
Set db = OpenDatabase(Name:=strDbName, _
Options:=True, ReadOnly:=False)
Set qry = db.QueryDefs(strQryName )
2) Now plug in the parameter values. Lets's assume here you need to fill
10 parameters. Let's also assume you've loaded these parameter values in
an array.
For x=1 to 10
strParam = "Param" & Trim(Str(x))
qry.Parameters(strParam) = MyArray(x)
Next x
3) Now you run the query, whch creates a Recordset object containing
your data. You copy this Recordset into Excel.
Set rs = qry.OpenRecordset(dbOpenSnapshot)
Set rngCopySpot = _
Workbooks("MyBook").Sheets("MySheet").Range("MyRange")
rngCopySpot.CopyFromRecordset rs
4) Done.
rs.Close
Set rs=Nothing
db.Close
Set db=Nothing
This is just a rough outline of the code you'll need, of course. but
hopefully it will give you some idea of what's involved. Once you
establish a reference to the DAO or ADO libraries, you'll have full
access to the help files, which can be very useful. If you need help
creating a parameter query in Access, consult the Access help files.
