Call SQL Server Stored Procedures with parameters,

[KCSL]

We have a block of code that looks like this:

With ActiveSheet.QueryTables.Add(Connection:="ODBC;DSN=XXX",
Destination:=Range("A1"))
.CommandText = "spsReport17" ' Array("SELECT * FROM tblReport17")

.Name = "XXX"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
End With

..CommandText used to just be a 'SELECT * FROM...' statment but we are having
to change to Stored Procedures - and this worked ok.
However, we now need to pass a single parameter to this Stored Proc.

Can anybody tell me the syntax (or even if this is possible) please.
I've looked in the help and can't find anything about this.

 

[Rob Bovey]

Hi KCSL,

Yes you can call a stored procedure when creating a QueryTable, you just
have to treat the stored procedure and any arguments to it as if it were any
other SQL string. Here's an example:

Sub QueryWithStoredProc()
Dim objQT As QueryTable
Set objQT = Sheet1.QueryTables.Add("ODBC;DSN=XXX;", _
Sheet1.Range("A1"))
objQT.CommandText = "spMyStoredProc 'TextArgument'"
objQT.CommandType = xlCmdSql
objQT.Refresh False
End Sub

--
Rob Bovey, Excel MVP
Application Professionals
http://www.appspro.com/

* Take your Excel development skills to the next level.
* Professional Excel Development
http://www.appspro.com/Books/Books.htm

 

[Scott]

Hi,
I have a similar problem, but I can't seem to get the parameter to work. I
can run the stored procedure without any problem but I don't understand how
or what kind of text arguement is needed. Can you give an example of this?
(I have tried where clauses but they don't work)

Thanks,
Scott

 

[Rob Bovey]

Hi Scott,

I'm not totally sure what you're looking for. I've shown an example
below that calls a stored procedure and passes it one text argument. I've
hard-coded the argument value in this case in order to make the syntax more
clear, but it could just as easily have been specified using a variable or a
cell reference.

--
Rob Bovey, Excel MVP
Application Professionals
http://www.appspro.com/

* Take your Excel development skills to the next level.
* Professional Excel Development
http://www.appspro.com/Books/Books.htm