Autofilter method in VBSCRIPT

[torontozfinest]

2 Apr 2003, 12:30 PM


Autofilter method in VBSCRIPT
Post #1

torontoz_finest
Newcomer

Joined: Apr 2003
Location: toronto
Hey Folks ,


Im having a bit of trouble with the autofilter method in vbscript. Basically I am trying to filter a spreadsheet for certain information, and gather that information for
manipulation but in ever attempt with the current way I am using the method (as well as the many other ways i attempted) it does not seem to filter to the script. Below
is the coding im using (with some editing of non relevant areas). Im trying to sort data in the third field.


Function testqueue (stQueue,stAreaCode,stGB,iTestResults)
Dim ExcelRow,obXL


ExcelRow = "1"
Set obXL = CreateObject("Excel.Application" )
obXL.WorkBooks.Open ("test.xls" )
obXL.Application.DisplayAlerts = False


obXL.worksheets(stQueue).Range("A1" ).AutoFilter 3,stGB

Do While obXl.worksheets(stQueue).cells(ExcelRow,1).value <> &quot;&quot;

If obXl.worksheets(stQueue).cells(ExcelRow,4).value = day(date) Then
If cstr(trim(obXl.worksheets(stQueue).cells(ExcelRow,6).value)) = &quot;Y&quot; Then
iTestResults(1) = iTestResults(1) + 1
Else
iTestResults(2) = iTestResults(2) + 1
End If

Elseif obXl.worksheets(stQueue).cells(ExcelRow,4).value = day(dateadd(&quot;d&quot;,1,Date)) Then

If cstr(trim(obXl.worksheets(stQueue).cells(ExcelRow,6).value)) = &quot;Y&quot; Then
iTestResults(3) = iTestResults(3) + 1
Else
iTestResults(4) = iTestResults(4) + 1
End If

End If

ExcelRow=ExcelRow + 1

Loop


obXL.worksheets(stQueue).Range(&quot;C1&quot; ).AutoFilter 3,&quot;&quot;


testqueue = iTestResults



obXl.WorkBooks.close
obXL.Application.DisplayAlerts = True
obXl = Null


End Function


To note the spread I am using is dynamically created. Here is a sample entry into the spreadsheet.

xxx-xxx-xxxx | |K1A |2 |1800 |Y
xxx-xxx-xxxx |S2 |K1A |2 |1900 |

(| denoting separate fields, x denoting a numeric value)

all fields are general minus the second which is text. The spread size is dynamic as well. I dont think it has anything to do with the spreadsheet itself, and all the
information being provided to the function to filter by IS correct. I figure it is just my coding of calling the method but i threw that in there anyways. any answer would be
EXTREMELY appreciated. THANKS ALOT!!!!!!