This is some example code I use for exporting financial reports to excel. It create a new workbook and populated a number of worksheets (I only show the first one (Overview) here). See the comments for what each section of code is doing. It does not include conditional formatting, but was all created from the record macro I mentioned and then adapted to what I required - you can do the same for conditional formatting.
This uses late binding, but you can add the excel library during development and convert back to late binding if required
Still a WIP for me, ultimately each section will be it's own function with the relevant parameters - which is why I use cells to specify ranges
Code:
Function createReport()
Dim xl As Object
Dim wb As Object
Dim ws As Object
Dim fn As String
Set xl = CreateObject("Excel.Application")
xl.Visible = True
Set wb = xl.Workbooks.Add
Set ws = wb.Worksheets(1)
popOverview ws
'freeze top row
With xl.ActiveWindow
.SplitColumn = 0
.SplitRow = 1
.FreezePanes = True
End With
'create the folder if does not exist
fn = Format(DLookup("AsAtDate", "tblConfigs"), "yyyy-mm")
If Dir(CurrentProject.Path & "\Reports\Financials " & fn, vbDirectory) = "" Then
MkDir CurrentProject.Path & "\Reports\Financials " & fn
DoEvents
MkDir CurrentProject.Path & "\Reports\Financials " & fn & "\Docs"
End If
'save the file
wb.Worksheets(1).Activate
wb.saveas FileName:= _
CurrentProject.Path & "\Reports\financials " & fn & "\" & fn & " Financials.xlsx", _
FileFormat:=51, CreateBackup:=False 'xlOpenXMLWorkbook=51
'ws.Close
Set ws = Nothing
wb.Close
End Function
Function popOverview(ws)
Dim rs As DAO.Recordset
Dim i As Integer
Dim r As Object
With ws
.Name = "Overview"
Set rs = CurrentDb.OpenRecordset("qryOverview")
For i = 0 To rs.Fields.Count - 1
.cells(1, i + 1).Value = rs.Fields(i).Name
Next i
.range("A2").CopyFromRecordset rs
'format header
'set filter on top row
.range(.cells(1, 1), .cells(44, 7)).AutoFilter 'row then column
'colour top row
With .range(.cells(1, 1), .cells(1, i)).interior
.Pattern = 1 'xlSolid
.ThemeColor = 3 'xlThemeColorDark2
.TintAndShade = -0.249977111117893
.PatternTintAndShade = 0
End With
'format value columns
.range(.Columns(1), .Columns(i)).EntireColumn.AutoFit
.range(.Columns(2), .Columns(i)).NumberFormat = "0.00"
'format total rows
Set r = .range(.cells(2, 1), .cells(.UsedRange.Rows.Count, rs.Fields.Count))
With r.Borders(8) 'xlEdgeTop=8
.LineStyle = 1 'xlContinuous
.Weight = 2 'xlThin
End With
With r.Borders(9) 'xlEdgeBottom=9
.LineStyle = -4119 'xlDouble
.Weight = 4 ' xlThick
End With
'remove autofilter
.UsedRange.AutoFilter Field:=1
End With
End Function