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.