Call SQL Server Stored Procedures with parameters,
 

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.

Call SQL Server Stored Procedures with parameters,
 

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

"KCSL" wrote in message
...
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.

Call SQL Server Stored Procedures with parameters,
 

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" wrote:

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

"KCSL" wrote in message
...
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.

Call SQL Server Stored Procedures with parameters,
 

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

"Scott" wrote in message
...
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" wrote:

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

"KCSL" wrote in message
...
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.