Pivot Table From Access Parameter Query

[Paul Smith]

I would like to built a Pivot which gets it's data from an MS Access
query which requires a parameter, which I would like to pass from
Excel.... is this possible?

if so, could someone please supply some sample code, the issue I am
having problems with is the parameter. I do not want to create the
pivot table or querytable using a SQL string from within Excel, I want
to use the Access Query.

Help or advise would be appreciated....fast [smile]

PWS

 

[Jamie Collins]

I would like to built a Pivot which gets it's data from an MS Access
query which requires a parameter, which I would like to pass from
Excel.... is this possible?

if so, could someone please supply some sample code, the issue I am
having problems with is the parameter. I do not want to create the
pivot table or querytable using a SQL string from within Excel, I want
to use the Access Query.

Funny you should ask this, for today I posted to dicks-blog the latest
in my 'ADO in just four lines' series:

http://www.dicks-blog.com/archives/2004/12/13/parameters-in-excel-external-data-queries/

Sub Just_Four_Lines()
Dim rs As Object
Set rs = CreateObject("ADODB.Recordset")
rs.Open _
"EXEC MyStoredProc †& _
Format$(Sheet1.Range("A1″).Value, "'yyyy-mm-dd'") & _
"," & _
Format$(Sheet1.Range("A2″).Value, "'yyyy-mm-dd'") & _
";", _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\MyJetDB.mdb"
Sheet2.Range("A1").CopyFromRecordset rs
End Sub

You will have to handle the parameters yourself. If you use a
querytable, i.e. MS Query and its quirky SQL SELECT syntax, you could
take advantage of its in-built support for parameters. You could still
use a querytable but you would have to change the parameter values in
the procedure call yourself.

Jamie.

--