OLE Excel AutoFilter

[Kenny]

Is there a way to filter out only columns that have values? (NonBlanks)
from Filter?

[highlight="perl"]
use Win32::OLE;
use strict;

my $EXCEL = Win32::OLE->new('Excel.Application','Quit');
$EXCEL->{'Visible'} = 1;

my $Workbook = $EXCEL->Workbooks->Open("C:\\Ex\\Test3.xls");

my $SheetSelect = $Workbook->Sheets("A");
$SheetSelect->Select;
my $Current_Sheet = $Workbook -> ActiveSheet;

# set bold & color
my $row=2;
my $value = $SheetSelect -> Range("B".$row) -> {'Value'};
while($value =~ /\w+/) {
my $colorRange = $SheetSelect->Range("B".$row.":C".$row);
$colorRange->{Font}->{Bold} = 1 ;
$colorRange->Interior->{ColorIndex} = 4;

$row++;
$value = $SheetSelect -> Range("B".$row) -> {'Value'};
}

# set auto fit & filter (display drop down menu list)
my $autoFit = $SheetSelect->Range("B2:C40");;
$autoFit->Columns()->Autofit();
###This is part where I am having problem.. I want to show only
NonBlanks
$autoFit->Columns()->AutoFilter("1","(NonBlanks)");
#this will diplay the rows have 100 as the value
# $autoFit->Columns()->AutoFilter("1","100");


## Save
$EXCEL->ActiveWorkbook->Save();
$Workbook->Save();
$Workbook->Quit();
Win32::OLE->FreeUnusedLibraries();
[/highlight]

Function AutoFilter([Field], [Criteria1], [Operator As
XlAutoFilterOperator = xlAnd], [Criteria2], [VisibleDropDown])

Any help? Thanks.

 

[NickHK]

Kenny,
The easiest to get an idea of the methods/object required is record a macro
in Excel first.
I get :
Selection.AutoFilter Field:=1, Criteria1:="<>"
So for you I guess something like :
$autoFit->Columns()->AutoFilter("1","<>");

NickHK

 

[Kenny]

Thanks NickHK. That's what I need.

Kenny,
The easiest to get an idea of the methods/object required is record a macro
in Excel first.
I get :
Selection.AutoFilter Field:=1, Criteria1:="<>"
So for you I guess something like :
$autoFit->Columns()->AutoFilter("1","<>");

NickHK

Is there a way to filter out only columns that have values? (NonBlanks)
from Filter?

[highlight="perl"]
use Win32::OLE;
use strict;

my $EXCEL = Win32::OLE->new('Excel.Application','Quit');
$EXCEL->{'Visible'} = 1;

my $Workbook = $EXCEL->Workbooks->Open("C:\\Ex\\Test3.xls");

my $SheetSelect = $Workbook->Sheets("A");
$SheetSelect->Select;
my $Current_Sheet = $Workbook -> ActiveSheet;

# set bold & color
my $row=2;
my $value = $SheetSelect -> Range("B".$row) -> {'Value'};
while($value =~ /\w+/) {
my $colorRange = $SheetSelect->Range("B".$row.":C".$row);
$colorRange->{Font}->{Bold} = 1 ;
$colorRange->Interior->{ColorIndex} = 4;

$row++;
$value = $SheetSelect -> Range("B".$row) -> {'Value'};
}

# set auto fit & filter (display drop down menu list)
my $autoFit = $SheetSelect->Range("B2:C40");;
$autoFit->Columns()->Autofit();
###This is part where I am having problem.. I want to show only
NonBlanks
$autoFit->Columns()->AutoFilter("1","(NonBlanks)");
#this will diplay the rows have 100 as the value
# $autoFit->Columns()->AutoFilter("1","100");


## Save
$EXCEL->ActiveWorkbook->Save();
$Workbook->Save();
$Workbook->Quit();
Win32::OLE->FreeUnusedLibraries();
[/highlight]

Function AutoFilter([Field], [Criteria1], [Operator As
XlAutoFilterOperator = xlAnd], [Criteria2], [VisibleDropDown])

Any help? Thanks.