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

"Matt Williamson" <(E-Mail Removed)> wrote in message
news:uf%(E-Mail Removed)...
>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/library/aa165427(office.10).aspx

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

<SNIP>

> Use:
>
> Do Until rst.EOF
>
> Per this link the method copies until the EOF property is True:
>
> http://msdn.microsoft.com/en-us/library/aa165427(office.10).aspx
>
> --
> Richard Mueller
> MVP Directory Services
> Hilltop Lab - http://www.rlmueller.net
> --
>
>

Thanks Richard. That works when I use loop until rst.eof. I thought for sure
I had tried that already. Apparently not.

"Matt Williamson" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> <SNIP>
>
>> Use:
>>
>> Do Until rst.EOF
>>
>> Per this link the method copies until the EOF property is True:
>>
>> http://msdn.microsoft.com/en-us/library/aa165427(office.10).aspx
>>
>> --
>> Richard Mueller
>> MVP Directory Services
>> Hilltop Lab - http://www.rlmueller.net
>> --
>>
>>
>
> Thanks Richard. That works when I use loop until rst.eof. I thought for
> sure I had tried that already. Apparently not.

Hmm. I did try it. When I said it worked, it was because I forgot to comment
out my error trap. rst.EOF doesn't do it either. Loop Until rst.State <> 1
seems to work though but I never would have figured that out by reading the
ado help on the state property of the recordset object. I just opened the
locals window and watched which properties changed as I stepped through the
code.