B
Bob S
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. 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):
----------------------------
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:
----------------------------
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.
Any help is greatly appreciated
The VBA code runs through a huge log file. 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):
----------------------------
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:
----------------------------
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.
Any help is greatly appreciated