I'm not a newbie to VB or ADO or Excel, but just to mixing them all. I'm
trying to get the output of an SQL Stored procedure to be the data for a
spreadsheet--the ultimate goal of which is so that I can put a button on a
spreadsheet for a user and tell them that they can update/run it for any set
of parameters that they desire. I can do the prompts and get the data thru
ADO into a recordset, but then how do I assign it to a range of cells?

TIA!

Bryan,

If you have Excel 2000 or later, you can use the CopyFromRecordset method to
drop the rs into a worksheet range

Worksheets("Sheet1").Range("A2").CopyFromRecordset oRS

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Bryan Dickerson" wrote in message
...
I'm not a newbie to VB or ADO or Excel, but just to mixing them all. I'm
trying to get the output of an SQL Stored procedure to be the data for a
spreadsheet--the ultimate goal of which is so that I can put a button on a
spreadsheet for a user and tell them that they can update/run it for any
set
of parameters that they desire. I can do the prompts and get the data
thru
ADO into a recordset, but then how do I assign it to a range of cells?

TIA!

Thanks!

Now, I have to confess that I *thought* I could do the ADO part, but I've
previously only worked with straight query text that is passed to the
server. This time I'm having to call a stored procedure as I indicated
below and I'm not having much luck. The SP has 3 parameters. Can you help
or do I need to take this to another NG?

This is what I currently have:
------------------------------
oCmd.CommandType = adCmdText
oCmd.CommandText = "EXEC SP_Report '" & _
CustomerNumber & "', '" &
_
StartDate & "', '" &
EndDate & "'"
Set oRSet = oCmd.Execute
------------------------------
.... it's not working. Any ideas?
TIA!

"Bob Phillips" wrote in message
...
Bryan,

If you have Excel 2000 or later, you can use the CopyFromRecordset method
to
drop the rs into a worksheet range

Worksheets("Sheet1").Range("A2").CopyFromRecordset oRS

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Bryan Dickerson" wrote in message
...
I'm not a newbie to VB or ADO or Excel, but just to mixing them all.
I'm
trying to get the output of an SQL Stored procedure to be the data for a
spreadsheet--the ultimate goal of which is so that I can put a button on
a
spreadsheet for a user and tell them that they can update/run it for any
set
of parameters that they desire. I can do the prompts and get the data
thru
ADO into a recordset, but then how do I assign it to a range of cells?

TIA!

Hi Bryan

search this string
How To Pull Access Query
in the "Search For" box on this page
date 12/7/2004
There were some suggestions close to your topic.
Post back if you need more information but you will need to
be a little more specific as to how far you are with your code.
Have you made a connection to the DB, have you made a
reference to the ADO active X library???

Good Luck
TK




"Bryan Dickerson" wrote:

Thanks!

Now, I have to confess that I *thought* I could do the ADO part, but I've
previously only worked with straight query text that is passed to the
server. This time I'm having to call a stored procedure as I indicated
below and I'm not having much luck. The SP has 3 parameters. Can you help
or do I need to take this to another NG?

This is what I currently have:
------------------------------
oCmd.CommandType = adCmdText
oCmd.CommandText = "EXEC SP_Report '" & _
CustomerNumber & "', '" &
_
StartDate & "', '" &
EndDate & "'"
Set oRSet = oCmd.Execute
------------------------------
.... it's not working. Any ideas?
TIA!

"Bob Phillips" wrote in message
...
Bryan,

If you have Excel 2000 or later, you can use the CopyFromRecordset method
to
drop the rs into a worksheet range

Worksheets("Sheet1").Range("A2").CopyFromRecordset oRS

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Bryan Dickerson" wrote in message
...
I'm not a newbie to VB or ADO or Excel, but just to mixing them all.
I'm
trying to get the output of an SQL Stored procedure to be the data for a
spreadsheet--the ultimate goal of which is so that I can put a button on
a
spreadsheet for a user and tell them that they can update/run it for any
set
of parameters that they desire. I can do the prompts and get the data
thru
ADO into a recordset, but then how do I assign it to a range of cells?

TIA!

Bryan,

What does it do instead of work?

Tim.


"Bryan Dickerson" wrote in message
...
Thanks!

Now, I have to confess that I *thought* I could do the ADO part, but I've
previously only worked with straight query text that is passed to the
server. This time I'm having to call a stored procedure as I indicated
below and I'm not having much luck. The SP has 3 parameters. Can you
help
or do I need to take this to another NG?

This is what I currently have:
------------------------------
oCmd.CommandType = adCmdText
oCmd.CommandText = "EXEC SP_Report '" & _
CustomerNumber & "', '"
&
_
StartDate & "', '" &
EndDate & "'"
Set oRSet = oCmd.Execute
------------------------------
... it's not working. Any ideas?
TIA!

This is my code (pared down to the important parts, of course):
--------------------------------------------------------------------------
Dim oCon As New ADODB.Connection
Dim oCmd As New ADODB.Command
Dim oRSet As ADODB.Recordset

ConnectionString = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist
Security Info=True;Initial Catalog=Mirror;Data Source=SQLServer;Use
Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Use Encryption
for Data=False;Tag with column collation when possible=False"

<snip Prompt for 3 parameters and verify </snip

oCon.Open ConnectionString
oCmd.CommandType = adCmdText
oCmd.CommandText = "EXEC SPReport '" & CustomerNumber & "', '" & _
StartDate & "', '" & EndDate & "'"
Set oRSet = oCmd.Execute
Worksheets("1032").Range("A1").CopyFromRecordset oRSet
--------------------------------------------------------------------------

Instead of work, it returns column headers, but no data in any columns.
Hmmm...


"Tim Williams" <saxifrax at pacbell dot net wrote in message
...
Bryan,

What does it do instead of work?

Tim.


"Bryan Dickerson" wrote in message
...
Thanks!

Now, I have to confess that I *thought* I could do the ADO part, but
I've
previously only worked with straight query text that is passed to the
server. This time I'm having to call a stored procedure as I indicated
below and I'm not having much luck. The SP has 3 parameters. Can you
help
or do I need to take this to another NG?

This is what I currently have:
------------------------------
oCmd.CommandType = adCmdText
oCmd.CommandText = "EXEC SP_Report '" & _
CustomerNumber & "',
'"
&
_
StartDate & "', '" &
EndDate & "'"
Set oRSet = oCmd.Execute
------------------------------
... it's not working. Any ideas?
TIA!

Are you fomatting the StartDate and EndDate strings correctly?
Jamie.

--