Cannot use recordset twice with copy From recordset

[JasonC]

Here is the snippet of code (some lines have been left out):

Dim conn As New ADODB.Connection
rs05.Open "SELECT * FROM [05 Store Count Summary]", conn, adOpenDynamic
Dim xlApp As New Excel.Application
With xlApp
.Visible = False
.Workbooks.Open xlPath & xlBkNamePRINT
Set xlBkPRINT = .ActiveWorkbook
.Workbooks.Open xlPath & xlBkNameQTD
Set xlBkQTD = .ActiveWorkbook
.Workbooks.Open xlPath & xlBkNameWK
Set xlBkWK = .ActiveWorkbook
End With

'Part A
xlBkWK.Activate
xlBkWK.Worksheets("Environmental Step 3").Activate
xlApp.Range("C5").CopyFromRecordset rs05

'Part B
xlBkQTD.Activate
xlBkQTD.Worksheets("Environmental Step 3").Activate
xlApp.Range("C5").CopyFromRecordset rs05

I have one recordset that is being pasted into two seperate worksheets. From
stepping through the code, Part A is being executed, but not Part B. There is
no error thrown, the workbook is activated, it goes to the respective tab,
but then nothing is pasted. When I rearrange Part A with Part B (Part B is
executed first), the code works for Part B but not A. This code was working
in the past, but something over the past year broke it. There is no error
being thrown. Thanks in advance for any insight into this issue.

 

[joel]

try adding

RS05.MoveFirst


I'm not sure why the code ever worked. Not sure if you are opening and
closing the recordset. A recordset is similar to a file which moves the
pointer as it move the data so when you done reading everything the
point is at the end of the file and you have to reset the pointer to the
beginning of the file before you can read the data a 2nd time.