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.

--

I have tried just taking the actual query out of the SP and making sure the
parms I pass will actually work when plugged in correctly. So the answer to
your question is yes. Now there is a part that I'm unsure of there. When I
just run the actual query outside of the SP, I put the dates in single
quotes ('), 'cause that's what works. When I pass the date strings in to
the SP, I pass them in with single quotes around them, so my question is, do
the single quotes become part of the query? I know that if I put single
quotes and double quotes around them, I get an error about converting
strings to smalldatetime format, so I don't think adding a layer of
dbl-quotes is the answer either.

Hmmm....

"onedaywhen" wrote in message
ups.com...
Are you fomatting the StartDate and EndDate strings correctly?
Jamie.

--

Bryan Dickerson wrote:
When I
just run the actual query outside of the SP, I put the dates in
single
quotes ('), 'cause that's what works. When I pass the date strings
in to
the SP, I pass them in with single quotes around them, so my question
is, do
the single quotes become part of the query?

Just to clarify, I'd expect the command text to look like this:

EXEC SPReport 'ABC123', '2004-12-01', '2004-12-31'

This assumes customer number is text; if it is numeric, don't put the
value in quotes.

Jamie.

--

You are correct in that all of the parameters are text and should be
enclosed in single quote marks in the actual query.

"onedaywhen" wrote in message
ups.com...

Bryan Dickerson wrote:
When I
just run the actual query outside of the SP, I put the dates in
single
quotes ('), 'cause that's what works. When I pass the date strings
in to
the SP, I pass them in with single quotes around them, so my question
is, do
the single quotes become part of the query?

Just to clarify, I'd expect the command text to look like this:

EXEC SPReport 'ABC123', '2004-12-01', '2004-12-31'

This assumes customer number is text; if it is numeric, don't put the
value in quotes.

Jamie.

--

Ok, thru lots of time away from the VBA, I think I've made progress, but I
still have errors. The progress is that getting to the point of
"oCommand.Execute" with "EXEC SPReport 'ABC123', '2004-12-01', '2004-12-31'"
in the oCommand.Text works and even the Execute method works (or at least
doesn't return an error). But I put a breakpoint at the next statement:
Worsheet("Sheet1").Range("A1").CopyFromRecordset oRS" and did some playing
with the oRS object. When I put "? oRS.Fields.Count" it gave me the error
message: "Run-time error '3704' Operation is not allowed when the object is
closed." Hmmm....??? I'm currently still even in break mode on the VBA and
any attempt to display any properties or execute any methods on the oRS
object produces this error. Strange. This is my 1 remaining piece to the
puzzle for this project. NE1 got NE ideas??

Thanx!

"Bryan Dickerson" wrote in message
...
You are correct in that all of the parameters are text and should be
enclosed in single quote marks in the actual query.

"onedaywhen" wrote in message
ups.com...

Bryan Dickerson wrote:
When I
just run the actual query outside of the SP, I put the dates in
single
quotes ('), 'cause that's what works. When I pass the date strings
in to
the SP, I pass them in with single quotes around them, so my question
is, do
the single quotes become part of the query?

Just to clarify, I'd expect the command text to look like this:

EXEC SPReport 'ABC123', '2004-12-01', '2004-12-31'

This assumes customer number is text; if it is numeric, don't put the
value in quotes.

Jamie.

--

Bryan Dickerson wrote:
the Execute method works (or at least
doesn't return an error). But I put a breakpoint at the next
statement:
Worsheet("Sheet1").Range("A1").CopyFromRecordset oRS" and did some
playing
with the oRS object. When I put "? oRS.Fields.Count" it gave me the
error
message: "Run-time error '3704' Operation is not allowed when the
object is
closed."

Are you sure you are associating the recordset with the returned rowset
(I'm assuming you stored proc *does* return data)? I rarely use a
Command object myself so I'd do something like:
Set oRS = oConnection.Execute("EXEC blah...")

Jamie.

--

I tried exactly what you suggested and again, the query ran (I tried running
in MS Query Analyzer what the Excel VBA was trying to run and it ran
correctly and returned rows), but when I put a debugging break in the code
at the next statement and used the immediate window, I tried oRS.MoveFirst
and got the error "Run-Time error 3704: Operation is not allowed when the
object is closed." That seems awfully strange--I shouldn't have to 'open'
the recordset object after it's created, should I? BTW, I get basically the
same error regardless of what command I attempt on the recordset object or
what property I attempt to view.

"onedaywhen" wrote in message
oups.com...
Bryan Dickerson wrote:
the Execute method works (or at least
doesn't return an error). But I put a breakpoint at the next
statement:
Worsheet("Sheet1").Range("A1").CopyFromRecordset oRS" and did some
playing
with the oRS object. When I put "? oRS.Fields.Count" it gave me the
error
message: "Run-time error '3704' Operation is not allowed when the
object is
closed."

Are you sure you are associating the recordset with the returned rowset
(I'm assuming you stored proc *does* return data)? I rarely use a
Command object myself so I'd do something like:
Set oRS = oConnection.Execute("EXEC blah...")

Jamie.

--

Oh, BTW, I thought I would include that I am able to use this query as the
basis for a spreadsheet ("Data, Import External Data"). So I can update the
parameters of the EXEC SPReport call and run it that way. This VBA stuff is
mostly for "User Friendliness", so I am getting results returned, but for
some reason I'm just having problems in the VBA getting the Recordset to
dump into the spreadsheet
(Worksheets.Item("Sheet1").Range("A1").CopyFromRec ordset oRS). Does that
jog anyone's memory? Does the fact that I'm only specifying a range of "A1"
have anything to do with it?

"Bryan Dickerson" wrote in message
...
I tried exactly what you suggested and again, the query ran (I tried
running
in MS Query Analyzer what the Excel VBA was trying to run and it ran
correctly and returned rows), but when I put a debugging break in the code
at the next statement and used the immediate window, I tried oRS.MoveFirst
and got the error "Run-Time error 3704: Operation is not allowed when the
object is closed." That seems awfully strange--I shouldn't have to 'open'
the recordset object after it's created, should I? BTW, I get basically
the
same error regardless of what command I attempt on the recordset object or
what property I attempt to view.

"onedaywhen" wrote in message
oups.com...
Bryan Dickerson wrote:
the Execute method works (or at least
doesn't return an error). But I put a breakpoint at the next
statement:
Worsheet("Sheet1").Range("A1").CopyFromRecordset oRS" and did some
playing
with the oRS object. When I put "? oRS.Fields.Count" it gave me the
error
message: "Run-time error '3704' Operation is not allowed when the
object is
closed."

Are you sure you are associating the recordset with the returned rowset
(I'm assuming you stored proc *does* return data)? I rarely use a
Command object myself so I'd do something like:
Set oRS = oConnection.Execute("EXEC blah...")

Jamie.

--

Good News!! (to which everyone says "Finally!!", then mutters "...maybe
he'll go away...") I finally found the answer he
http://support.microsoft.com/kb/q235340/ . I added "SET NOCOUNT ON" into
the query and away it runs!! Woohoo!! But now I have more questions:

1. How do I get the VBA to clear the spreadsheet before the query so that
previous results are not mixed with more current results?
2. How I get my VBA to retain the formatting that I have set in place?

TIA!

Anyone? Anyone? Bueller? Bueller?

"Bryan Dickerson" wrote in message
...
Good News!! (to which everyone says "Finally!!", then mutters "...maybe
he'll go away...") I finally found the answer he
http://support.microsoft.com/kb/q235340/ . I added "SET NOCOUNT ON" into
the query and away it runs!! Woohoo!! But now I have more questions:

1. How do I get the VBA to clear the spreadsheet before the query so that
previous results are not mixed with more current results?
2. How I get my VBA to retain the formatting that I have set in place?

TIA!