VBA Access code for Conditional formatting in Excel

Question

I have a database that exports a table to Excel and I need the cell formats in Column Z to be either Currency or Percentage based upon the value in Column Y. I know I can do this in Excel with conditional formatting, but I am stumped on how to do it in Access.

I tried this

Sub GetExcel_INV_Hist(File_Path_Name)

Dim MyXL As Object

Set MyXL = CreateObject("Excel.Application")
    MyXL.Workbooks.Open (File_Path_Name)

    MyXL.Visible = True

' IHFormat Macro

MyXL.Application.Columns("Z:AC").Select
MyXL.Application.Selection.NumberFormat = "$#,##0.00_);[Red]($#,##0.00)"

MyXL.Application.Range("Z:Z").Select
MyXL.Application.Selection.NumberFormat = "0.00%"
MyXL.Application.Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
    "=$Y3 = ""GM"""
MyXL.Application.Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
MyXL.Application.ExecuteExcel4Macro "File_Path_Name!(2,1,""0.00%"")"
MyXL.Application.Selection.FormatConditions(1).StopIfTrue = False
MyXL.Application.Selection.Copy
MyXL.Application.Range("Z4").Select
MyXL.Application.Range(Selection, Selection.End(xlDown)).Select
MyXL.Application.Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
    SkipBlanks:=False, Transpose:=False

Which first changed the format of Z to currency, then I selected the first cell, Z3, checked to see if Y3 = GM and if it did, changed the format to percent. Then select the rest of Z and did a Paste Special/Format. It throws an error at the ExecuteExcel4Macro line. I'm assuming because the conditional formatting wasn't done on the workbook the process creates.

Thanks in advance for any help.

Edit: I recorded the macro in excel, so the line in question was created as part of the excel macro. I've expanded my code and this is the error:

Errorsnip

The rest of the formatting works perfectly.

Thanks again

Access can call anything in the Excel object library like conditional formatting and vice versa. So if Excel can do it, Access can do it. But we need more information here. What is ExecuteExcel4Macro? Is this an Excel macro? What is the error? Include fuller block so we can see context. — Parfait
@Parfait Application.ExecuteExcel4Macro, but I agree, it's not clear what this has to do with the conditional formatting. — BigBen
Access won't understand what xlExpression means - you'll need to update that to 2 (the numerical equivalent of xlExpression). — Darren Bartrup-Cook

Darren Bartrup-Cook Darren Bartrup-Cook · Accepted Answer · 2018-10-10T15:36:48

I guess you're having trouble keeping track of what's selected, and Access hasn't got a clue what xlExpression (2), xlPasteFormats (-4122) or xlNone (-4142) mean. You'll need to use the numerical equivalent instead.

I was surprised by the MyXL.Application.ExecuteExcel4Macro "File_Path_Name!(2,1,""0.00%"")" line, but that's what I got when I recorded a macro to do it as well. I'd still throw that line out and use NumberFormat = "£#,##0.00" instead.

Try this code:

Sub Test()

    GetExcel_INV_Hist "C:\Path_To_My_Workbook\Book1.xlsx"

End Sub

Public Sub GetExcel_INV_Hist(File_Path_Name As String)

    Dim MyXL As Object
    Dim MyWB As Object

    Set MyXL = CreateXL

    Set MyWB = MyXL.Workbooks.Open(File_Path_Name)

    With MyWB.worksheets("Sheet1")
        .Columns("AA:AC").NumberFormat = "$#,##0.00_);[Red]($#,##0.00)"
        .Columns("Z:Z").NumberFormat = "0.00%"

        With .Columns("Z:Z")
            .FormatConditions.Add Type:=2, Formula1:="=$Y1=""GM""" '2 = xlExpression
            .FormatConditions(.FormatConditions.Count).SetFirstPriority
            With .FormatConditions(1)
                .NumberFormat = "$#,##0.00"
            End With
        End With

    End With

End Sub

Public Function CreateXL(Optional bVisible As Boolean = True) As Object

    Dim oTmpXL As Object

    On Error Resume Next
    Set oTmpXL = GetObject(, "Excel.Application")

    If Err.Number <> 0 Then
        Err.Clear
        On Error GoTo ERROR_HANDLER
        Set oTmpXL = CreateObject("Excel.Application")
    End If

    oTmpXL.Visible = bVisible
    Set CreateXL = oTmpXL

    On Error GoTo 0
    Exit Function

ERROR_HANDLER:
        MsgBox "Error " & Err.Number & vbCr & _
            " (" & Err.Description & ") in procedure CreateXL."
End Function

VBA Access code for Conditional formatting in Excel

1 Answers