Excel Automation - formula / Formatting / filter

[Rx_]

The first code is just some Excel Automation tips. Put the record count of the recordset returned for use in custom formula later.
Start Data return in Row 5 of Excel Worksheet. Then add the data filter and freeze payne. On Row 1 of Excel, add Title information.
On Row 2, 3, 4 of Excel - add some custom formulas.
These are examples of formulas that acknowledge the rows hidden with the column filters in Excel.
This allows end users to dynamically filter columns and see the resulting totals.
Then, code that bolds a item change in a column. A font being bold can also be filtered.

490       If ObjXL Is Nothing Then
500           Set ObjXL = New Excel.Application
510           ObjXL.EnableEvents = False
....
' strSQLWF = "Select From .... " ' your favorite SQL Statement
Set rsReclMonDt = CurrentDb.OpenRecordset(strSQLWF, dbOpenSnapshot, dbReadOnly)
630           intWorksheetNum = 1
660         intRowPos = 6 ' put Data at row 6
680         ObjXL.DisplayAlerts = False    ' Turn off Display Alerts
690         ObjXL.Worksheets(intWorksheetNum).Cells(intRowPos, 1).CopyFromRecordset rsReclMonDt
' Count the record returned to Excel
710         intMaxRecordCount = rsReclMonDt.RecordCount - 1 
730       For intHeaderColCount = 0 To intMaxheaderColCount
740           If Left(rsReclMonDt.Fields(intMaxheaderColCount).Name, 3) <> "xxx" Then  ' Future use - adding xxx in future cross tab queries for fields to exclude
750               ObjXL.Worksheets(intWorksheetNum).Cells(intRowPos - 1, intHeaderColCount + 1) = rsReclMonDt.Fields(intHeaderColCount).Name    ' Relative to intRowPos - Create header from SQL
760           End If
770       Next intHeaderColCount
780       ObjXL.Rows((intRowPos - 1) & ":" & (intRowPos - 1)).Select  ' 
' ... with the above selection - format the header row
1370    ObjXL.Rows((intRowPos - 1) & ":" & (intRowPos - 1)).Select   
1380    ObjXL.Selection.AutoFilter        
1390    ObjXL.Rows((intRowPos) & ":" & (intRowPos)).Select                            ' first Data row to freeze Payne
1400    ObjXL.ActiveWindow.FreezePanes = True ' what a pane!

'Basically, a query was run against Access and the data moved to Excel to start on Row 5 (intRowPosition).
In the Rows 2, 3, 4 - the next code will create a formula.
The intMaxRecordCount (records returned count) will be used to customize the formula

In Row 1 column G - date the Report
3680 ObjXL.Range("G1").Select
3690 ObjXL.ActiveCell.FormulaR1C1 = "Report Run: " & Format(Now(), "MM/DD/YY")

Next, some Row titles to explain the formulas:
3700 ObjXL.Range("C2").Select
3710 ObjXL.ActiveCell.FormulaR1C1 = "Maximum Days"
3720 ObjXL.Range("C3").Select
3730 ObjXL.ActiveCell.FormulaR1C1 = "Minimum Days"
3731 ObjXL.Range("C4").Select
3732 ObjXL.ActiveCell.FormulaR1C1 = "Average Days"
' now Column F and L Row 3
3740 ObjXL.Range("F3").Select
3750 ObjXL.ActiveCell.FormulaR1C1 = "= AGGREGATE(5,3,R[2]C:R[8115]C)"
3751 ObjXL.Range("L3").Select
3752 ObjXL.ActiveCell.FormulaR1C1 = "= AGGREGATE(5,3,R[2]C:R[8115]C)"
' Now Row 2 Max formula and a Average formula

            ObjXL.Range("K2").Select
            ObjXL.ActiveCell.FormulaR1C1 = "= AGGREGATE(4,3,R[3]C:R[8116]C)"
            ObjXL.Range("L2").Select
            ObjXL.ActiveCell.FormulaR1C1 = "= AGGREGATE(4,3,R[3]C:R[8116]C)"
' note 5 (min) and 4 (max) for Aggregate - Note the R[2] and R[3] row offset so the formula starts at the same location.
' the R[8116] is a fixed number. It could also use a variable
R[ & introwposition + 6 & ]
 
' For average:
                            ObjXL.Range("K4").Select
                            ObjXL.ActiveCell.Formula = "= SUBTOTAL(101, K6:K" & intMaxRecordCount + 6 & ")"
                            ObjXL.Range("L4").Select
                            ObjXL.ActiveCell.Formula = "= SUBTOTAL(101, L6:L" & intMaxRecordCount + 6 & ")"

After bringing the data to Excel, the first set of code shows how to set the Filter on each column and the Freeze Payne.

It is important to note, these formulas only show the Min/Mas and Average for the visible rows below when any combination of filters are selected on the rows.

Wait a minute! You are now telling me that the Query sorted the first column as Primary Key and the Second Column as Customer Name (Sorted A-Z). Column C has the Inventory Last Conducted Date (sorted by latest) Column D has the Inventory Total. Column C & D are many to the Row B Customer Name.

What you would like to do is Bold the first Customer Name, then lighten any other associated Customer Names, then Bold the next changed customer name and so on.

1430    With ObjXL.ActiveWorkbook.ActiveSheet
          'objxl.ActiveWorkbook.ActiveSheet
1440      For i = intRowPos To intMaxRecordCount + intRowPos
1450          If .Cells(i, "B").value <> .Cells(i - 1, "B").value Then
1460              .Range(.Cells(i, "B"), .Cells(i, "E")).Font.FontStyle = "Bold"
1470          Else
1480               .Range(.Cells(i, "B"), .Cells(i, "E")).Font.ColorIndex = 16 'metallic gray
1490          End If
1500      Next i
1510  End With

This will bold any change in Column B - bold columns B to E, then lighten any repeating customer names.

Better yet, now the Excel column filter can be Filtered by Font (bold) by the users. The formulas on row 2, 3, 4 will only calculate the rows visible after the column filter is used.