Bob Strack
2006-06-30 15:45:00 UTC
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
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