Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Pivotcharts and stored procedures | Charts and Charting in Excel | |||
Can I import SQL Stored Procedures into Excel? | Excel Discussion (Misc queries) | |||
Running sql stored procedures from Excel | Excel Discussion (Misc queries) | |||
Stored Procedures - First Attempt | Excel Programming | |||
Stored Procedure call and passing parameters | Excel Programming |