MS Access uses Remote services to create an Excel worksheet from scratch, import over 20,000 rows (from Oracle/SQL Server) and add all of the formatting, filter and other features of an interactive report. The question is Should I use VBA to set cell background or can someone suggest how to use built in functions (e.g. conditional formatting).
The final Excel workbook is automated to save as with no Macros.
See image
There are about 20,000 sites (1 record per site). Each site has a Status.
Each Site, Status record has about 15 columns.
Each of the cells in the 15 columns have a True / False.
The True / False is based on a complex set of business rules that are Functions in SQL Server. The functions have multiple parameters and multiple logic with a return of True or False.
The top row is the Status expected results (T, F, N) for that status column to pass.
The T expects a True below, the F expects a False below, while the N is neither (e.g. it doesn't matter).
All of this is automated from Access with vba code, SQL Server linked tables, and Excel Object VBA.
Objective:
The yellow iin the attachment is just for demo purposes.
For Each Row, for Each Status, take the Expected columns (T,F,N)
Take each Status - if the T matches a True or if F matches a False - then turn the row/column backgound Green. Else turn backgound Red.
The N stay White regardless of True/False in the row (they don't matter).
Question:
My plan is to have Access use VBA to loop through each record, look up the Status, then color each cell according to the Expected Status.
(see code example). It could be the 20,000 rows X 15 columns.
Excel built-in formulas run much faster than VBA code.
Can anyone suggest a solution to use built in Excel functions?
For example: Filter each Status - then apply a Conditional Format to each column.
This is the code I will modify to include Status then format based on the Expected Status. It will have additional loops of course. But in theory will be based on this.
Code:
'2930 With objxl.ActiveWorkbook.ActiveSheet
' 'objxl.ActiveWorkbook.ActiveSheet
'2940 For i = intRowPos To intMaxRecordCount + intRowPos
'2950 If .Cells(i, "B").Value <> .Cells(i - 1, "B").Value Then
'2960 .Range(.Cells(i, "B"), .Cells(i, "E")).Font.FontStyle = "Bold"
'2970 '.Cells(i, 33).Value = .Cells(i, 3).Value
'2980 Else
'2990 .Range(.Cells(i, "B"), .Cells(i, "E")).Font.ColorIndex = 16 'metalic gray
'3000 End If
'3010 Next i
'3020 End With
Last edited:
