Pivot Table Stored Proc with Parameter

[BBM]

HI All,


I want to create a data connection which I will use for a pivot table.

THis is fine if I want a static SP with no parameters, but I would like to be able to specify a parameter (a year, e.g. int 2009) and bind its value to a cell on a sheet.

I'm using Excel 2007 btw.

Is this possible? I cant see how!

Thanks,
BBM

[geekgirlau]

Sub Connect()
Dim cn As ADODB.Connection
Dim cmd As ADODB.Command
Dim rec As ADODB.Recordset
Dim strConn As String
Dim strSQL As String

Set cn = New ADODB.Connection

strConn = "My Connection String"

'open the connection.
cn.Open strConn
Set cmd = New Command

With cmd
.ActiveConnection = cn
.CommandTimeout = 30
.CommandText = "SP_Name Parameter"
.CommandType = adCmdStoredProc

Set rec = .Execute
End With

ActiveSheet.Range("A1").CopyFromRecordset rec

ExitHere:
On Error Resume Next
rec.Close
cn.Close
Set rec = Nothing
Set cn = Nothing
End Sub