Export to an Excel file with conditional formatting

[bjornas]

Hello,

I want to export an Access query to an Excel-file. This is unproblematic. However, I also want to use conditional formatting in the exported Excel-file. I can do this manually, but any formatting will be overwritten the next time I export the query to Excel.

Is there any way to retain the formatting in the exported Excel file?

Rgds.,

Bjørn O

[CJ_London]

Short answer is no. When you next export then when you manually format , use the macro recorder. When done it will generate the code which you can use as an example

bacj in access after you have exported the query you can then use code to open the file and apply the code generated in your excel macro. It will no doubt need some modification in terms of objects but should give you an idea as to what is required

on my phone at the moment so can’t help more than that

[Welshgasman]

Perhaps use a template with the CF set, then copy that to a new file and export to that?

Eedit: Ah, I see the issue. I have always used Excel automation so would resue the same file.

That could be a solution, using copy from recordset perhaps?

[CJ_London]

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

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

[bjornas]

Hello and thank you for your tips. I will try them all - I try to teach myself Access by playing around with it.