Newbie at mixing ADO and Excel

[Bryan Dickerson]

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!

 

[Bob Phillips]

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]

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

 

[TK]

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

 

[Tim Williams]

Bryan,

What does it do instead of work?

Tim.

 

[Bryan Dickerson]

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...

Bryan,

What does it do instead of work?

Tim.

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:

'"

 

[Bryan Dickerson]

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....

 

[Bryan Dickerson]

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

 

[Bryan Dickerson]

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]

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.

 

[Bryan Dickerson]

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").CopyFromRecordset 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]

Good News!! (to which everyone says "Finally!!", then mutters "...maybe
he'll go away...") I finally found the answer here:
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!

 

[Bryan Dickerson]

Anyone? Anyone? Bueller? Bueller?