Microsoft Excel 2007 - "Parameters are not allowed in queries that can

[JBourne77]

Question: I am trying to pass a Stored Procedure through an Excel worksheet with the following SQL and getting this Error - "Parameters are not allowed in queries that can't be displayed graphically".

My code is this - EXEC cusExcelMTD, ?, ?

I have seen that I need to use VB to alter my Excel spreadsheet, but I have no clue as to how to do this or the coding. The two parameters I want to pass are "StartDate" and "EndDate". Can someone please lend me hand?

 

[xlbo]

To add parameters to stored procs, you need to use a contstruct like the following:

Dim cmdl As ADODB.Command

Set strCon = New ADODB.Connection

strCon.Open "Connection String here"

'Create a new command object to process the stored proc
Set cmdl = New ADODB.Command
    
With cmdl
    .ActiveConnection = strCon
    .CommandText = "Name Of Stored Proc"
    .CommandType = adCmdStoredProc
    .Parameters.Refresh
    'Add parameters to stored proc
    .Parameters(1).Value = Param1Value
    .Parameters(2).Value = Param2Value
    Set rsRecSet = .Execute()
End With

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question