K
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]
Any help? Thanks.
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]
Code:
Function AutoFilter([Field], [Criteria1], [Operator As
XlAutoFilterOperator = xlAnd], [Criteria2], [VisibleDropDown])
Any help? Thanks.