Autofiltering Excel with multiple filter conditions

Question

I am trying to autofilter in Excel using the below VBScript code. This script called multiple times from a Perl program.

Dim objExcel : Set objExcel = GetObject(,"Excel.Application") 
objExcel.Visible = True
objExcel.Selection.AutoFilter
objExcel.ActiveSheet.Range("G1").AutoFilter WScript.Arguments.Item(0), _
    WScript.Arguments.Item (1)

Now I would like to know: is there a way by which I can pass an array for WScript.Arguments.Item (1) so that all the conditions are selected in one go? The task is to delete the filtered value. I call this script through Perl multiple times and the above script filter one value at a time and delete. The program works fine, but is slow.

Following is the part of Perl which calls the VBScript.

while(<FILE>){
    chomp;
    system("CSCRIPT "."\"$currentWorkingDirectory\"".'aboveVBS.vbs 9 '."\"$_\"");
    sleep(2);
}

Answer 1

If you put quotes around the values, VBScript will treat it as a single argument.

> cscript script.vbs arg1 "multiple values for arg 2"

In the script:

WScript.Echo WScript.Arguments.Count    ' ==> 2

a = Split(WScript.Arguments(1))
WScript.Echo a(0)                        ' ==> multiple
WScript.Echo a(1)                        ' ==> values
WScript.Echo a(2)                        ' ==> for
WScript.Echo a(3)                        ' ==> arg
WScript.Echo a(4)                        ' ==> 2

Excel expects:

Range.AutoFilter <Field>, <Criteria>, <Operator>

If you want a list of criteria to filter on, you'll use xlFilterValues for the <Operator> argument. <Criteria> will be an array of string values, which we created above.

Const xlFilterVaues = 7

objExcel.ActiveSheet.Range("G1").AutoFilter WScript.Arguments.Item(0), a, xlFilterValues

So, just try adding Split() around WScript.Arguments(1) in your existing code, and pass xlFilterValues for the third param.

Answer 2

If only your second argument changes, you could pass the entire content of your data file to the VBScript:

local $/;
my $args = <FILE>;
$args =~ s/^\s+|\s+$//g;
$args =~ s/\r?\n/" "/g;

system("cscript \"$currentWorkingDirectory\\your.vbs\" 9 \"$args\"");

and change the processing in your VBScript to this:

Set xl = CreateObject("Excel.Application") 
xl.Visible = True

Set wb = xl.Workbook.Open("C:\path\to\your.xlsx")
Set ws = wb.Sheets(1)
...
xl.Selection.AutoFilter

For i = 1 To WScript.Arguments.Count - 1
  ws.Range("G1").AutoFilter WScript.Arguments(0), WScript.Arguments(i)
  ...
Next

Or you could simply call the VBScript with the field and the path to the data file:

system("cscript \"$currentWorkingDirectory\\your.vbs\" 9 \"$filepath\"");

and do all the processing in VBScript:

Set xl = CreateObject("Excel.Application") 
xl.Visible = True

Set wb = xl.Workbook.Open("C:\path\to\your.xlsx")
Set ws = wb.Sheets(1)
...
xl.Selection.AutoFilter

Set fso = CreateObject("Scripting.FileSystemObject")
Set f   = fso.OpenTextFile(WScript.Arguments(1))
Do Until f.AtEndOfStream
  ws.Range("G1").AutoFilter WScript.Arguments(0), f.ReadLine
  ...
Next
f.Close

Applying more than 2 AutoFilter conditions to a column at the same time is not possible. Check the signature of the AutoFilter method in the documentation:

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

expression An expression that returns a Range object.

You have Critera1 and Criteria2 and an Operator for combining the two. Calling the AutoFilter method with another set of criteria replaces the existing criteria.