Copying recordset multiple times using copyfromrecordset

J

JasonC

We recently caught this issue but found out after research, that this has
been occuring all year. This code worked in the past, but I am not sure if
there was an Excel update that occurred which changed the behavior of the
script. What we are trying to do is to have a recordset pasted twice into two
seperate worksheets. It is pasting without issue in the first
copyfromrecordset, but then it skips over the second paste (even when
stepping through the code). I know that it is not the worksheet we are
copying into because I can reverse the 'first paste/second paste' (see label
in code below) code and the problem then occurs with the first paste . Here
are code snippets that isolate the problem area:

Dim conn As New ADODB.Connection


rs05.Open "SELECT * FROM [Store Query]", conn, adOpenDynamic

With xlApp
.Visible = True
.Workbooks.Open xlPath & xlBkNamePRINT
Set xlBkPRINT = .ActiveWorkbook
.Workbooks.Open xlPath & xlBkNameQTD
Set xlBkQTD = .ActiveWorkbook
.Workbooks.Open xlPath & xlBkNameWK
Set xlBkWK = .ActiveWorkbook
End With

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

'Second paste - this works when i paste it above the first paste snippet
xlBkQTD.Activate
xlBkQTD.Worksheets("Environmental Step 3").Activate
xlApp.Range("C5").CopyFromRecordset rs05
 
D

Daryl S

JasonC -

Can you tell us where in the code the last statement that executed
successfully was, the first statement that is 'skipped', and the first
statement that is executed successfully after the 'skipped' code? Please
post all the code in that section, and let us know which lines are 'skipped'.
 
J

JasonC

Hi Daryl-

Thanks for the response!


When I am stepping through the code, and place a stop in the part A
'activate line' code, hit F8, and as soon as I hit F8 again on the
'xlApp.Range("C5").CopyFromRecordset rs05' line, the report actually runs to
completion unless i put a stop immediately after the Part B code. When I put
in the stop after the Part B code, the line skips over all three lines of the
Part B code and stops. When I place a stop in each line of the Part B code,
it stops at each line and allows me to step through it.

Here is the code:

Sub Update_Excel_Workbook()

On Error GoTo errHandler

frmDECOMP.chkImport_Data.Value = True
DoEvents
frmDECOMP.chkUpdate_Data.Value = True
DoEvents

Dim xlBkNameWK As String
Dim xlBkNameQTD As String
Dim xlBkNamePRINT As String
Dim xlPath As String
Dim xlRptPath As String
Dim dbPath As String

xlPath = LPath
xlRptPath = LPath & "XLReports\"
dbPath = LPath & DB

'*** USE ADO TO UPDATE SPREADSHEETS ***
Dim conn As New ADODB.Connection
Dim rs05 As New ADODB.Recordset
Dim rs01 As New ADODB.Recordset
Dim rs02 As New ADODB.Recordset
Dim rs03 As New ADODB.Recordset
Dim rs04 As New ADODB.Recordset
Dim rsDATE As New ADODB.Recordset

'*** CONNECT TO DATABASE ***
conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=" & dbPath
& ";"

'*** FILL RECORDSETS ***
rs05.Open "SELECT * FROM [05 Store Count Summary]", conn, adOpenDynamic
rs01.Open "SELECT * FROM [01 Environmental Stores]", conn, adOpenDynamic
rs02.Open "SELECT * FROM [02 Environmental Merch Div] WHERE
MER_DVS_DES_TXT Not Like '%ADJ BUDGET BALANCING - 7%'", conn, adOpenKeyset
rs03.Open "SELECT * FROM [03 QTD Environmental Stores]", conn,
adOpenDynamic
rs04.Open "SELECT * FROM [04 QTD Environmental Merch Div] WHERE
MER_DVS_DES_TXT Not Like '%ADJ BUDGET BALANCING - 7%'", conn, adOpenKeyset
rsDATE.Open "SELECT * FROM qryGetDate", conn, adOpenDynamic

'*** GET CURRENT FISCAL WEEK NUMBER ***
week_num = rsDATE.Fields(0).Value

'*** OPEN EXCEL WORKBOOKS ***
Dim xlBkWK As Excel.Workbook
Dim xlBkQTD As Excel.Workbook
Dim xlBkPRINT As Excel.Workbook
Dim xlApp As New Excel.Application

xlBkNameWK = "Store Environmental Summary WK Template.xls"
xlBkNameQTD = "Store Environmental Summary QTD Template.xls"
xlBkNamePRINT = "Store Environmental Summary PRINT Template.xls"

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

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

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

xlBkWK.Activate
xlBkWK.Worksheets("Environmental Step 3").Activate
xlApp.Range("A14").CopyFromRecordset rs01

xlBkWK.Activate
xlBkWK.Worksheets("Environmental Step 3").Activate
xlApp.Range("A24:AG47").ClearContents
Dim rngeW As Integer
Select Case rs02.RecordCount
Case 20
rngeW = 28
Case 21
rngeW = 27
Case 22
rngeW = 26
Case 23
rngeW = 25
Case 24
rngeW = 24
Case 25
rngeW = 24
Case Else
rngeW = 28
End Select
xlApp.Range("A" & rngeW).CopyFromRecordset rs02


Daryl S said:
JasonC -

Can you tell us where in the code the last statement that executed
successfully was, the first statement that is 'skipped', and the first
statement that is executed successfully after the 'skipped' code? Please
post all the code in that section, and let us know which lines are 'skipped'.

--
Daryl S


JasonC said:
We recently caught this issue but found out after research, that this has
been occuring all year. This code worked in the past, but I am not sure if
there was an Excel update that occurred which changed the behavior of the
script. What we are trying to do is to have a recordset pasted twice into two
seperate worksheets. It is pasting without issue in the first
copyfromrecordset, but then it skips over the second paste (even when
stepping through the code). I know that it is not the worksheet we are
copying into because I can reverse the 'first paste/second paste' (see label
in code below) code and the problem then occurs with the first paste . Here
are code snippets that isolate the problem area:

Dim conn As New ADODB.Connection


rs05.Open "SELECT * FROM [Store Query]", conn, adOpenDynamic

With xlApp
.Visible = True
.Workbooks.Open xlPath & xlBkNamePRINT
Set xlBkPRINT = .ActiveWorkbook
.Workbooks.Open xlPath & xlBkNameQTD
Set xlBkQTD = .ActiveWorkbook
.Workbooks.Open xlPath & xlBkNameWK
Set xlBkWK = .ActiveWorkbook
End With

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

'Second paste - this works when i paste it above the first paste snippet
xlBkQTD.Activate
xlBkQTD.Worksheets("Environmental Step 3").Activate
xlApp.Range("C5").CopyFromRecordset rs05
Click to expand...
Click to expand...
 
J

JasonC

This worked! I wonder if there was an update applied that changed the
behavior of the code requiring this addition. Thanks!

Tim Williams said:
try

rs05.Movefirst

before the second CopyFromRecordset


Tim


JasonC said:
We recently caught this issue but found out after research, that this has
been occuring all year. This code worked in the past, but I am not sure if
there was an Excel update that occurred which changed the behavior of the
script. What we are trying to do is to have a recordset pasted twice into
two
seperate worksheets. It is pasting without issue in the first
copyfromrecordset, but then it skips over the second paste (even when
stepping through the code). I know that it is not the worksheet we are
copying into because I can reverse the 'first paste/second paste' (see
label
in code below) code and the problem then occurs with the first paste .
Here
are code snippets that isolate the problem area:

Dim conn As New ADODB.Connection


rs05.Open "SELECT * FROM [Store Query]", conn, adOpenDynamic

With xlApp
.Visible = True
.Workbooks.Open xlPath & xlBkNamePRINT
Set xlBkPRINT = .ActiveWorkbook
.Workbooks.Open xlPath & xlBkNameQTD
Set xlBkQTD = .ActiveWorkbook
.Workbooks.Open xlPath & xlBkNameWK
Set xlBkWK = .ActiveWorkbook
End With

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

'Second paste - this works when i paste it above the first paste snippet
xlBkQTD.Activate
xlBkQTD.Worksheets("Environmental Step 3").Activate
xlApp.Range("C5").CopyFromRecordset rs05
Click to expand...


.
Click to expand...
 
T

Tim Williams

I remember that from way back, so I don't think it's new.

Tim

JasonC said:
This worked! I wonder if there was an update applied that changed the
behavior of the code requiring this addition. Thanks!

Tim Williams said:
try

rs05.Movefirst

before the second CopyFromRecordset


Tim


JasonC said:
We recently caught this issue but found out after research, that this
has
been occuring all year. This code worked in the past, but I am not sure
if
there was an Excel update that occurred which changed the behavior of
the
script. What we are trying to do is to have a recordset pasted twice
into
two
seperate worksheets. It is pasting without issue in the first
copyfromrecordset, but then it skips over the second paste (even when
stepping through the code). I know that it is not the worksheet we are
copying into because I can reverse the 'first paste/second paste' (see
label
in code below) code and the problem then occurs with the first paste .
Here
are code snippets that isolate the problem area:

Dim conn As New ADODB.Connection


rs05.Open "SELECT * FROM [Store Query]", conn, adOpenDynamic

With xlApp
.Visible = True
.Workbooks.Open xlPath & xlBkNamePRINT
Set xlBkPRINT = .ActiveWorkbook
.Workbooks.Open xlPath & xlBkNameQTD
Set xlBkQTD = .ActiveWorkbook
.Workbooks.Open xlPath & xlBkNameWK
Set xlBkWK = .ActiveWorkbook
End With

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

'Second paste - this works when i paste it above the first paste
snippet
xlBkQTD.Activate
xlBkQTD.Worksheets("Environmental Step 3").Activate
xlApp.Range("C5").CopyFromRecordset rs05
Click to expand...


.
Click to expand...
Click to expand...
 
Top