Scripting Excel Autofilter Problem

[Bob Strack]

Help,

I am trying to port a VBA script to VBscript and I am stump on the autofilter syntax.

The VBA code runs through a huge log file. It autofilters the different computers and colorizes the
cells. I've tried stepping through the rows, but there are over 250,000 rows in the workbook. The
VBA autofilter script takes seconds.

VBA Code snip (sort of - the computer array is auto-generated elsewhere):
----------------------------
c =0
ColorNumbs = Array(33, 34, 35, 36, 37, 38, 39, 44, 45, 46)
ListComp = Array("luke","obiwan","CP30","hans") 'for example
For Each Compu In ListComp
Selection.AutoFilter Field:=5, Criteria1:=Compu
ActiveSheet.AutoFilter.Range.SpecialCells(xlCellTypeVisible).Select
With Selection.Interior
.ColorIndex = ColorNumbs(c)
.Pattern = xlSolid
End With
c = c + 1
Next Compu
-------------------------------------
The VBA code works;

When I try and translate it to VBscript I get:
VBscript snip
----------------------------
Set objLOGxls = CreateObject("Excel.Application")
strExcelPath = "c:\temp\logout.xls"
objLOGxls.Workbooks.Open(strExcelPath)
c =0
ColorNumbs = Array(33, 34, 35, 36, 37, 38, 39, 44, 45, 46)
ListComp = Array("luke","obiwan","CP30","hans") 'for example
For Each Compu In ListComp
objLOGxls.Selection.AutoFilter,5,Compu
objLOGxls.ActiveSheet.AutoFilter.Range.SpecialCells(xlCellTypeVisible).Select
objLOGxls.Selection.Interior.ColorIndex = ColorNumbs(c)
objLOGxls.Selection.Interior.Pattern = xlSolid
c = c + 1
Next Compu
---------------------------------------
When I run the VBscript, I get an error at the Autofilter line which reads "Excel: AutoFilter method of Range class failed."

I've tried dozens of permutation on this theme and I can't get it to work.

Is the VBA to VBscript translation wrong? Does Autofilter have some, object/property/method I'm using incorrectly?

Any help is greatly appreciated

[ekkehard.horner]

Post by Bob Strack
Help,
I am trying to port a VBA script to VBscript and I am stump on the autofilter syntax.
The VBA code runs through a huge log file. It autofilters the different
computers and colorizes the
cells. I've tried stepping through the rows, but there are over 250,000
rows in the workbook. The
VBA autofilter script takes seconds.
----------------------------
c =0
ColorNumbs = Array(33, 34, 35, 36, 37, 38, 39, 44, 45, 46)
ListComp = Array("luke","obiwan","CP30","hans") 'for example
For Each Compu In ListComp
Selection.AutoFilter Field:=5, Criteria1:=Compu
ActiveSheet.AutoFilter.Range.SpecialCells(xlCellTypeVisible).Select
With Selection.Interior
.ColorIndex = ColorNumbs(c)
.Pattern = xlSolid
End With
c = c + 1
Next Compu
-------------------------------------
The VBA code works;
VBscript snip
----------------------------
Set objLOGxls = CreateObject("Excel.Application")
strExcelPath = "c:\temp\logout.xls"
objLOGxls.Workbooks.Open(strExcelPath)
c =0
ColorNumbs = Array(33, 34, 35, 36, 37, 38, 39, 44, 45, 46)
ListComp = Array("luke","obiwan","CP30","hans") 'for example
For Each Compu In ListComp
objLOGxls.Selection.AutoFilter,5,Compu

the first "," in the line above looks fishy to me; I assume the
xlXXX constants are (correctly) defined?

Post by Bob Strack
objLOGxls.ActiveSheet.AutoFilter.Range.SpecialCells(xlCellTypeVisible).Select
objLOGxls.Selection.Interior.ColorIndex = ColorNumbs(c)
objLOGxls.Selection.Interior.Pattern = xlSolid
c = c + 1
Next Compu
---------------------------------------
When I run the VBscript, I get an error at the Autofilter line which
reads "Excel: AutoFilter method of Range class failed."
I've tried dozens of permutation on this theme and I can't get it to work.
Is the VBA to VBscript translation wrong? Does Autofilter have some,
object/property/method I'm using incorrectly?
Any help is greatly appreciated

[Bob Strack]

Ekkehard,

Thank you for the reply. The constants seem to be correct (they run in VBA). The VBA help for AutoFilter Method says:
expression.AutoFilter(Field, Criteria1, Operator, Criteria2, VisibleDropDown).

From your suggestion, I tried using paranthesis, equal, space and nothing, as a pointer to the method's arguments but
they all fail with different messages:

Comma - Microsoft Office Excel: AutoFilter method of Range class failed

Paranthesis - Microsoft VBScript compilation error: Cannot use parentheses when calling a Sub
Equal sign - Microsoft VBScript compilation error: Expected end of statement
Space - Ignores the arguments and script doesn't work
No Space - Microsoft VBScript runtime error: Object doesn't support this property or method: 'Selection.Autofilter5'


I've tried setting a variable to a returned object:
"set objLOGfil = objLOGxls.Selection.Autofilter,5,Compu" which generates a compilation error

Any other help would be greatly appreciated

Post by ekkehard.horner
the first "," in the line above looks fishy to me; I assume the
xlXXX constants are (correctly) defined?

[ekkehard.horner]

Post by Bob Strack
Ekkehard,
Thank you for the reply. The constants seem to be correct (they run in
expression.AutoFilter(Field, Criteria1, Operator, Criteria2,
VisibleDropDown).
From your suggestion, I tried using paranthesis, equal, space and
nothing, as a pointer to the method's arguments but they all fail with
Comma - Microsoft Office Excel: AutoFilter method of Range class failed
Paranthesis - Microsoft VBScript compilation error: Cannot use
parentheses when calling a Sub
Equal sign - Microsoft VBScript compilation error: Expected end of statement
Space - Ignores the arguments and script doesn't work
No Space - Microsoft VBScript runtime error: Object doesn't support this
property or method: 'Selection.Autofilter5'
"set objLOGfil = objLOGxls.Selection.Autofilter,5,Compu" which generates
a compilation error
Any other help would be greatly appreciated

Post by ekkehard.horner
the first "," in the line above looks fishy to me; I assume the
xlXXX constants are (correctly) defined?

From the VBA Docs:

Ausdruck.AutoFilter(Field, Criteria1, Operator, Criteria2, VisibleDropDown)

Your (working) VBA code:

Selection.AutoFilter Field:=5, Criteria1:=Compu

Your VBS code:

objLOGxls.Selection.AutoFilter,5,Compu

I'd try:

objLOGxls.Selection.AutoFilter 5, Compu

[Bob Strack]

Ekkehard,

Thanks for the response. If I run the code as you've written it, I get back:

(72, 9) Microsoft VBScript runtime error: Object required: 'Selection.AutoFilter'

This might not be a bad thing, but I don't know how to pass the arguments as an object to the autofilter. Any suggestions?

Thank you for your time

Post by ekkehard.horner
Ausdruck.AutoFilter(Field, Criteria1, Operator, Criteria2,
VisibleDropDown)
Selection.AutoFilter Field:=5, Criteria1:=Compu
objLOGxls.Selection.AutoFilter,5,Compu
objLOGxls.Selection.AutoFilter 5, Compu

[ekkehard.horner]

Post by Bob Strack
Ekkehard,
'Selection.AutoFilter'
This might not be a bad thing, but I don't know how to pass the
arguments as an object to the autofilter. Any suggestions?

[...]
The error message means that there is something wrong with
the Selection; either there is no Selection or it doesn't
contain data; arguments to Autofilter shouldn't be objects.

This is working VBScript code:

Dim objLOGxls, strExcelPath, c, ColorNumbs, ListComp, Compu
Dim oWB

Const xlCellTypeVisible = 12
Const xlSolid = 1

Set objLOGxls = CreateObject( "Excel.Application" )
strExcelPath = "<FSpec to .xls with data on sheet 1>"
Set oWB = objLOGxls.Workbooks.Open( strExcelPath )

c = 0
ColorNumbs = Array( 33, 34, 35, 36, 37, 38, 39, 44, 45, 46 )
ListComp = Array( "luke","obiwan","CP30","hans" ) 'for example
For Each Compu In ListComp
objLOGxls.Sheets( 1 ).Cells.AutoFilter 5, Compu
objLOGxls.Sheets( 1 ).AutoFilter.Range.SpecialCells(xlCellTypeVisible).Select
objLOGxls.Selection.Interior.ColorIndex = ColorNumbs(c)
objLOGxls.Selection.Interior.Pattern = xlSolid
c = c + 1
Next ' Compu

oWB.Close
objLOGxls.Quit

try to replace Sheets( 1 ) resp. Sheets( 1 ).Cells to something
sensible for your problem.

[Bob Strack]

Ekkehard,

Thank you very much. You set me on the correct path. I now have a working Vbscript to parse a log file, create an
Excel spreadsheet and populate the sheets with color coded entries.

Here are the final autofiltering lines:
'objLJsh are sheets from a Worksheets collection
'kComp is a dynamically created array of all computers in certain column
arrColors is a handwritten array of a dozen Range.Interior.ColorIndex values

For Each objLJsh in objLJxls.Workbook.Worksheets
'color sheets
c = 0
For Each Compu In kComp
objLJsh.Cells.Autofilter 5, Compu
objLJsh.autofilter.Range.Interior.ColorIndex = arrColors(c)
objLJsh.autofilter.Range.Interior.Pattern = 1
c = c + 1
Next
objLJsh.cells.AutoFilter 5
objLJsh.Cells.AutoFilter 6, "EventLog,6006"
objLJsh.AutoFilter.Range.Columns(1).Interior.ColorIndex = 22
objLJsh.AutoFilter.Range.Columns(2).Interior.ColorIndex = 22
objLJsh.AutoFilter.Range.Columns(3).Interior.ColorIndex = 22
objLJsh.AutoFilter.Range.Columns(4).Interior.ColorIndex = 22
objLJsh.Cells.AutoFilter 6, "EventLog,6005"
objLJsh.AutoFilter.Range.Columns(1).Interior.ColorIndex = 50
objLJsh.AutoFilter.Range.Columns(2).Interior.ColorIndex = 50
objLJsh.AutoFilter.Range.Columns(3).Interior.ColorIndex = 50
objLJsh.AutoFilter.Range.Columns(4).Interior.ColorIndex = 50
objLJsh.cells.Autofilter 6
objLJsh.Cells.AutoFilter 7,"Error"
objLJsh.Autofilter.Range.Columns(7).Interior.ColorIndex = 3
objLJsh.cells.AutoFilter 7,"Warning"
objLJsh.Autofilter.Range.Columns(7).Interior.ColorIndex = 6
objLJxls.ActiveSheet.Rows("1:1").Font.Bold = True
objLJxls.ActiveSheet.Rows("1:1").Interior.ColorIndex = -4142 'Constant for xlnone
objLJsh.cells.AutoFilter 7, "Error", 2, "Warning"

Next

I really appreciate your help

Bob

Post by ekkehard.horner
[...]
The error message means that there is something wrong with
the Selection; either there is no Selection or it doesn't
contain data; arguments to Autofilter shouldn't be objects.

.......