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

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

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