Create XLS with conditional formatting

ino_mart

Registered User.
Local time
Today, 08:30
Joined
Oct 7, 2009
Messages
78
All

I need to create an Access module which exports some data into an Excel 2007-file. I also need to define a conditional formatting. I started by recording the macro in Excel. Next I copied it into an Access module and tried to get it work.

My current code in the Access module:
Code:
Dim oExcel, obook, osheet As Object
    
Set oExcel = CreateObject("Excel.Application")
Set obook = oExcel.Workbooks.Add
Set osheet = obook.Worksheets(1)
    
osheet.Range("A1").FormatConditions.AddIconSetCondition
osheet.Range("A1").FormatConditions(1).SetFirstPriority

With osheet.Range("A1").FormatConditions(1)
    .ReverseOrder = False
    .ShowIconOnly = False
    .IconSet = xl3Symbols2
End With
    
osheet.Range("A1").FormatConditions(1).IconCriteria(1).Icon = xlIconGreenCheck
With osheet.Range("A1").FormatConditions(1).IconCriteria(2)
    .Type = xlConditionValueNumber
    .Value = 2440
    .Operator = 5
    .Icon = xlIconYellowExclamation
End With
    
With osheet.Range("A1").FormatConditions(1).IconCriteria(3)
    .Type = xlConditionValueNumber
    .Value = 3000
    .Operator = 7
    .Icon = xlIconRedCross
End With
The code first fails on
Code:
osheet.Range("A1").FormatConditions(1).IconCriteria(1).Icon  = xlIconGreenCheck
When I remark that line, the code continues until
Code:
.Icon = xlIconYellowExclamation
Same issue happens some lines below.

The IconSet = xl3Symbols2 creates in the cell a green circle (<2440), a yellow triangle (=> 2440 and < 3000) or a red square (=>3000) followed by the effective value.

An easy solution is just to show the values in green, yellow or red color. However, I am obliged to use the green circle, yellow triangle and red square as extra indicator.

Does someone know how to solve this (or an alternative with those indicators)?

Regards
Ino
 
I'm not strong in Excel stuff, but I can say that when using late binding as you are, you typically can't use constants like xlIconGreenCheck. If you run the code in Excel after setting a breakpoint, you should be able to find out what the numerical equivalent of xlIconGreenCheck is, and use that instead of the constant.
 
It's also in help by the way. xlIconGreenCheck is 22.
 
Thank, this did solve my problem.

I changed

  • xlIconGreenCheck to 22
  • xlIconYellowExclamation to 23
  • xlIconRedCross to 24
and now I get the expected (and wanted) output.

Regards
Ino
 

Users who are viewing this thread

Back
Top Bottom