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

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,Customer,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

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,Customer,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