"Matt Williamson" wrote in message
...
I have a stored proc on a sql server that is just a series of select
queries. Right now, it's 7 but I'll be adding more as time goes on. I using
ado in Excel 2003 to dump the results into the excel spreadsheet using
copyfromrecordset. This works fine if I know the number of recordsets and
write a block of code for each one e.g

' Set rst = rst.nextrecordset
' lLastRow = ActiveSheet.Range("a65536").End(xlUp).Row
' Range("A" & lLastRow).Offset(2, 0).CopyFromRecordset rst
' Set rst = rst.nextrecordset
' lLastRow = ActiveSheet.Range("a65536").End(xlUp).Row
' Range("A" & lLastRow).Offset(2, 0).CopyFromRecordset rst
' Set rst = rst.nextrecordset
' lLastRow = ActiveSheet.Range("a65536").End(xlUp).Row
' Range("A" & lLastRow).Offset(2, 0).CopyFromRecordset rst
' Set rst = rst.nextrecordset
' lLastRow = ActiveSheet.Range("a65536").End(xlUp).Row
' Range("A" & lLastRow).Offset(2, 0).CopyFromRecordset rst
' Set rst = rst.nextrecordset
' lLastRow = ActiveSheet.Range("a65536").End(xlUp).Row
' Range("A" & lLastRow).Offset(2, 0).CopyFromRecordset rst

But since I'm adding to it over time, I'm trying to make it dynamic and
just loop through each recordset. The code I came up with to do that is:

Set cmd = New ADODB.Command
cmd.ActiveConnection = cn
cmd.CommandText = "Compare_APX_UDA"
cmd.CommandType = adCmdStoredProc

Set rst = cmd.Execute()

Range("A8").CopyFromRecordset rst

Do
lLastRow = ActiveSheet.Range("a65536").End(xlUp).Row
Range("A" & lLastRow).Offset(2, 0).CopyFromRecordset rst
Set rst = rst.NextRecordset
Loop Until rst Is Nothing

Set rst = Nothing
Set cmd = Nothing
Set cn = Nothing

But the recordset doesn't get set to nothing when it reaches the end and
it gives me the error:

Error 3407: Operation not allow when object is closed.

I've tried Do until rst is nothing : loop as well but there is no change.
I know I can trap the error and just dump out there but I'd like to know
how this is supposed to work. I haven't found any examples that use
copyfromrecordset and nextrecordset to figure out the right way to do it.

How do I break out of the loop when I get to the last recordset?

TIA

Matt

Use:

Do Until rst.EOF

Per this link the method copies until the EOF property is True:

http://msdn.microsoft.com/en-us/libr...ffice.10).aspx

--
Richard Mueller
MVP Directory Services
Hilltop Lab - http://www.rlmueller.net
--