Excel Automation can't make Formatconditions work

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

hello,

I'm writting some vba code in acces that automates an excel spreadsheet,
part of the code places a conditional format on a column of cells in excel.
the code runs fine when i'm in excel however in access i does not run. here's
the code:

Dim OXL As Object
Set OXL = CreateObject("Excel.Application")
OXL.Workbooks.Open filename:="c:\filetest.xls"
OXL.Range("I:I").Select
OXL.Selection.FormatConditions.Add Type:=xlCellValue,
Operator:=xlBetween, _
Formula1:="1", Formula2:="109575"
OXL.Range("I:I").FormatConditions(1).Interior.ColorIndex = 43
OXL.Range("I:I").FormatConditions(1).Font.Bold = True
OXL.Quit
Set OXL = Nothing

my original code obviously does much more than this but i just wanted to
show the specific seciton that isn't working, the fifth line is where the
error occurs,
OXL.Selection.FormatConditions.Add Type:=xlCellValue,
Operator:=xlBetween, _
Formula1:="1", Formula2:="109575"

in debug mode, when i hold my mouse over xlCellValue and xlBetween a small
tooltip box shows "xlCellValue = empty" and "xlBetween = empty" respectively.

thanks
Mike
 
Both xlCellValue and xlBetwwen are intrinsic constants only in Excel and VBA
(in Access context) and Access don't know them.

Replace them with explicit value 1 (you can find the values of intrinsic
constants using the Excel Object Library).

If you have added Excel Object Library into the References of your database,
you can use the intrinsic constants but you need full references like:

Excel.XlFormatConditionType.xlCellValue

and

Excel.XlFormatConditionOperator.xlBetween
 
Back
Top