Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copying recordset multiple times using copyfromrecordset
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copying recordset multiple times using copyfromrecordset
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" wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copying recordset multiple times using copyfromrecordset
try
rs05.Movefirst before the second CopyFromRecordset Tim "JasonC" wrote in message ... 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copying recordset multiple times using copyfromrecordset
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" wrote: 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" wrote: 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copying recordset multiple times using copyfromrecordset
This worked! I wonder if there was an update applied that changed the
behavior of the code requiring this addition. Thanks! "Tim Williams" wrote: try rs05.Movefirst before the second CopyFromRecordset Tim "JasonC" wrote in message ... 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 . |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copying recordset multiple times using copyfromrecordset
I remember that from way back, so I don't think it's new.
Tim "JasonC" wrote in message ... This worked! I wonder if there was an update applied that changed the behavior of the code requiring this addition. Thanks! "Tim Williams" wrote: try rs05.Movefirst before the second CopyFromRecordset Tim "JasonC" wrote in message ... 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 . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
copying a range of data multiple times from multiple workbooks | Excel Programming | |||
Copying specific cell ranges from a worksheet multiple times to a newsheet | Excel Programming | |||
CopyFromRecordset with Filtered ADO recordset | Excel Programming | |||
copying a recordset into a variable | Excel Programming | |||
CopyFromRecordset will fail if the recordset array data such as hierarchical recordsets ??? | Excel Programming |