Adding a conditional format to an excel report via vbscript

Discussion:

(too old to reply)

Gilliland, Gill

2007-07-31 14:45:36 UTC

I've found plenty of references to add a conditional format to an excel
sheet using VBA, but I cannot figure out how to translate that to vbscript.

The following is a VBA example that would alternate row colors. I would like
to set this formatcondition using vbscript:

Selection.FormatConditions.Delete
Selection.FormatConditions.Add Type:=xlExpression, _
Formula1:="=MOD(ROW(),2)=1"
Selection.FormatConditions(1).Interior.ColorIndex = 15

Thanks for any help,
Gill

J.Williams

2007-08-01 00:31:54 UTC

Permalink

With VBScript, where you have named arguments like Type:=, you have to drop
the names and place the arguments in the order expected by the method.

For example, the FormatConditions Add method has the following arguments:

expression.Add(Type, Operator, Formula1, Formula2)

With Excel VBA, if you use named arguments you can specify these arguments
in any order and you can also omit optional arguments. With VBScript
however, you can't use named arguments and therefore must specify them in
the correct order and use commas to denote missing optional arguments.

Try this:

'Excel 2003 constants from
http://msdn2.microsoft.com/en-us/library/aa221100(office.11).aspx

const xlExpression = 2

Set oExcel = CreateObject("Excel.Application")
Set oWks = oExcel.Workbooks.Add()

oExcel.Range("A1:D20").Select
Set oSelection = oExcel.Selection

oSelection.FormatConditions.Delete

'2nd argument (Operator) of Add method is ignored if 1st argument (Type) is
xlExpression

oSelection.FormatConditions.Add xlExpression, , "=MOD(ROW(),2)=1"
oSelection.FormatConditions(1).Interior.ColorIndex = 15

oExcel.ActiveWorkbook.SaveAs "test.xls"
oExcel.ActiveWorkbook.Close(0)
oExcel.Quit

Post by Gilliland, Gill
I've found plenty of references to add a conditional format to an excel
sheet using VBA, but I cannot figure out how to translate that to vbscript.
The following is a VBA example that would alternate row colors. I would like
Selection.FormatConditions.Delete
Selection.FormatConditions.Add Type:=xlExpression, _
Formula1:="=MOD(ROW(),2)=1"
Selection.FormatConditions(1).Interior.ColorIndex = 15
Thanks for any help,
Gill

Gilliland, Gill

2007-08-01 18:47:44 UTC

Permalink

Absolutely perfect! Thanks for the explanation and web reference!

Gill

Post by J.Williams
With VBScript, where you have named arguments like Type:=, you have to drop
the names and place the arguments in the order expected by the method.
expression.Add(Type, Operator, Formula1, Formula2)
With Excel VBA, if you use named arguments you can specify these arguments
in any order and you can also omit optional arguments. With VBScript
however, you can't use named arguments and therefore must specify them in
the correct order and use commas to denote missing optional arguments.
'Excel 2003 constants from
http://msdn2.microsoft.com/en-us/library/aa221100(office.11).aspx
const xlExpression = 2
Set oExcel = CreateObject("Excel.Application")
Set oWks = oExcel.Workbooks.Add()
oExcel.Range("A1:D20").Select
Set oSelection = oExcel.Selection
oSelection.FormatConditions.Delete
'2nd argument (Operator) of Add method is ignored if 1st argument (Type) is
xlExpression
oSelection.FormatConditions.Add xlExpression, , "=MOD(ROW(),2)=1"
oSelection.FormatConditions(1).Interior.ColorIndex = 15
oExcel.ActiveWorkbook.SaveAs "test.xls"
oExcel.ActiveWorkbook.Close(0)
oExcel.Quit

angelo tavola

2023-02-17 22:28:55 UTC

Permalink