Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Best way to call a SQL Server Stored Procedure from within Excel
I am totally new to Excel programming, so please be patient with me while I
try and explain what it is that I need. I would like to call a SQL Server stored procedure from Excel and have the results placed in a specific range of cells, for example starting at D7 to K7 for however many rows are returned. So my question is this: What is the best way to call the SQL Server Stored Procedure and how do i place the results in a specific range of cells? Should I place a button on the page and call the proc on the click event? Any advise is greatly appreciated. -- Thanks, Scott |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Best way to call a SQL Server Stored Procedure from within Excel
I have always used the sql statement that the stored procedure uses which
made it considerably easier to get only the data i wanted to put in excel. here is an example: Dim cn As New ADODB.Connection Dim rs As New ADODB.Recordset Dim strsql As String Dim dbase As String Dim projec As String over: projec = TextBox1.Text dbase = Range("C1").Value cn.ConnectionString = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;" _ & "Initial Catalog=xxxx;Data Source=xxxxx" cn.Open cn.CursorLocation = adUseClient strsql = "SELECT PrgMgr1,Analyst,PrgGroup,ContAdmin,ProjectDesc,Cus tomer,ContractType,DateStart,DateClose,ContractNum FROM vblProject " _ & "WHERE HistoryName = '" & Range("G17") & "' AND Project = '" & rngProj & "'" If rs.State = adStateOpen Then rs.Close rs.Open strsql, cn If rs.RecordCount = 0 Then GoTo Title rs.MoveFirst Range("c10") = rs!ContAdmin Range("C9") = rs!PrgMgr1 Range("C11") = rs!Analyst rs.Close "SQLScott" wrote: I am totally new to Excel programming, so please be patient with me while I try and explain what it is that I need. I would like to call a SQL Server stored procedure from Excel and have the results placed in a specific range of cells, for example starting at D7 to K7 for however many rows are returned. So my question is this: What is the best way to call the SQL Server Stored Procedure and how do i place the results in a specific range of cells? Should I place a button on the page and call the proc on the click event? Any advise is greatly appreciated. -- Thanks, Scott |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Best way to call a SQL Server Stored Procedure from within Exc
Thanks dmoney, this is GREAT. I really appreciate it.
-- Thanks, Scott "dmoney" wrote: I have always used the sql statement that the stored procedure uses which made it considerably easier to get only the data i wanted to put in excel. here is an example: Dim cn As New ADODB.Connection Dim rs As New ADODB.Recordset Dim strsql As String Dim dbase As String Dim projec As String over: projec = TextBox1.Text dbase = Range("C1").Value cn.ConnectionString = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;" _ & "Initial Catalog=xxxx;Data Source=xxxxx" cn.Open cn.CursorLocation = adUseClient strsql = "SELECT PrgMgr1,Analyst,PrgGroup,ContAdmin,ProjectDesc,Cus tomer,ContractType,DateStart,DateClose,ContractNum FROM vblProject " _ & "WHERE HistoryName = '" & Range("G17") & "' AND Project = '" & rngProj & "'" If rs.State = adStateOpen Then rs.Close rs.Open strsql, cn If rs.RecordCount = 0 Then GoTo Title rs.MoveFirst Range("c10") = rs!ContAdmin Range("C9") = rs!PrgMgr1 Range("C11") = rs!Analyst rs.Close "SQLScott" wrote: I am totally new to Excel programming, so please be patient with me while I try and explain what it is that I need. I would like to call a SQL Server stored procedure from Excel and have the results placed in a specific range of cells, for example starting at D7 to K7 for however many rows are returned. So my question is this: What is the best way to call the SQL Server Stored Procedure and how do i place the results in a specific range of cells? Should I place a button on the page and call the proc on the click event? Any advise is greatly appreciated. -- Thanks, Scott |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how to execute a stored procedure in SQL server from Excel 2007? | Excel Programming | |||
Call a procedure whose name is stored in a variable | Excel Programming | |||
Run SQL Server stored procedure in Excel macro | Excel Programming | |||
How to pass an Excel range as an argument to a SQL Server stored Procedure | Excel Programming | |||
calling a stored procedure on MS SQL Server within MS Excel 2000 | Excel Programming |