Thanks for the previous comments. It helped me shape the design. The Excel Report generated from MS Access VBA is completed and working fine.
The only problem is that the code shown here for the loop takes about 20 minutes.
Any suggestion on more efficient way to accomplish the same thing would be appreciated.
This is a link to the Overview of the report:
This is a typical Excel Report generated with VBA from MS Access. The top represents a table for some of the Status.
The Rules 49..178 represent the Expected Result of a rule for that status. T = True, F = False and N = Neither T or F
So, when grading an Excel's Record's result - the top is the Expected (answer key) for each specific Status.
On the Excel Report, there is a list box. Choosing one Status - the colored Expectations are shown for that one status. Producing is chosen in this example. It is just to help users filter on one Status and look at the Expected Results as a visual tool.
In the Excel Data, Each Excel Record is matched to the Status Expectations. If T was expected and True is the answer - then the cell's background turns Green. If False was expected, and the data in the cell was False, then it turns Green. Green is a validation of the expected value. An Excel Record with all Green passes the Quality Assurance.
The VBA code goes through 20,000 records. For Each Excel record, it looks up the Status and the Expectation Key. Then, it validates each cell (column) in the record against the expected value. It turns the cell Green or Red based on that situation.
Some Records have a Status that doesn't apply, so it just skipps it (stays white).
The SQL Functions that create the data, the data pull, the basic Excel workbook creation takes 12 seconds.
The Code that changes the Cells into Red or Green takes 20 minutes.
The 20,000 Excel Records - times about 20 columns takes time, even a few cycles could speed things up.
Note: the ObjXL is just a reference from MS Access VBA to the Excel Application.
All of the code to generate Excel from scratch with automation is not shown. Up to this loop, it only takes 14 seconds to create.
The only problem is that the code shown here for the loop takes about 20 minutes.
Any suggestion on more efficient way to accomplish the same thing would be appreciated.
This is a link to the Overview of the report:
This is a typical Excel Report generated with VBA from MS Access. The top represents a table for some of the Status.
The Rules 49..178 represent the Expected Result of a rule for that status. T = True, F = False and N = Neither T or F
So, when grading an Excel's Record's result - the top is the Expected (answer key) for each specific Status.
On the Excel Report, there is a list box. Choosing one Status - the colored Expectations are shown for that one status. Producing is chosen in this example. It is just to help users filter on one Status and look at the Expected Results as a visual tool.
In the Excel Data, Each Excel Record is matched to the Status Expectations. If T was expected and True is the answer - then the cell's background turns Green. If False was expected, and the data in the cell was False, then it turns Green. Green is a validation of the expected value. An Excel Record with all Green passes the Quality Assurance.
The VBA code goes through 20,000 records. For Each Excel record, it looks up the Status and the Expectation Key. Then, it validates each cell (column) in the record against the expected value. It turns the cell Green or Red based on that situation.
Some Records have a Status that doesn't apply, so it just skipps it (stays white).
The SQL Functions that create the data, the data pull, the basic Excel workbook creation takes 12 seconds.
The Code that changes the Cells into Red or Green takes 20 minutes.
The 20,000 Excel Records - times about 20 columns takes time, even a few cycles could speed things up.
Note: the ObjXL is just a reference from MS Access VBA to the Excel Application.
All of the code to generate Excel from scratch with automation is not shown. Up to this loop, it only takes 14 seconds to create.
Code:
4570 With objxl.ActiveWorkbook.ActiveSheet
' intRowPos - Header started at Row 5 in Excel
4590 For i = intRowPos + 1 To intMaxRecordCount + intRowPos ' Excel Data records known from SQL Recordset count.
4600 WorksheetCellValue = Trim(.Cells(i, "Z").Value) ' Used to lookup Answer Key Number-
4620 Set OBJRS2_Record = CurrentDb.OpenRecordset("Select * from [RE_4SegMatrixLookupTable] where [ID_WellsStatus1] = " & WorksheetCellValue, dbOpenDynaset)
4630 If Not OBJRS2_Record.BOF Then ' is record empty
4640 For MatrixRuleColumn = 4 To 24 ' Read each rules column ' skip 3 "override indicator column"
4650 MatrixRuleValue = OBJRS2_Record.Fields(MatrixRuleColumn).Value
4660 WorksheetCellRowColumnValue = .Cells(i, MatrixRuleColumn + 1).Value ' the +1 syncs rule 158 table column with rule 158 excel column per row
4670 Select Case MatrixRuleValue ' looked up the Status - now look at the column expected value for the data in Excel for that column
' N is Neither True or False - so either value in Excel passes for this profile key
Case "N"
4680 .Cells(i, MatrixRuleColumn + 1).Interior.ColorIndex = 4 ' light green
4690 Case "T"
4700 If WorksheetCellRowColumnValue = "True" Then
' T matches with True - turn background Green
4710 .Cells(i, MatrixRuleColumn + 1).Interior.ColorIndex = 4 ' light green
' for testing, change to Bold - then add backgroun later
4720 Else
' T expected true - turn background Red
4730 .Cells(i, MatrixRuleColumn + 1).Interior.ColorIndex = 3 ' light Red
4740 End If
4750 Case "F"
4760 If WorksheetCellRowColumnValue = "False" Then
' F matches with False - this passes expectations turn background Green
4770 .Cells(i, MatrixRuleColumn + 1).Interior.ColorIndex = 4 ' light green ' light Green
4780 Else
4790 .Cells(i, MatrixRuleColumn + 1).Interior.ColorIndex = 3 ' light Red
4800 End If
4810 End Select
4820 Next MatrixRuleColumn
4830 End If
4840 Next i
4850 End With
