OLE Excel AutoFilter
 

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.

OLE Excel AutoFilter
 

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" wrote in message
oups.com...
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]

Code:

---

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


---

Any help? Thanks.

OLE Excel AutoFilter
 

Thanks NickHK. That's what I need.

NickHK wrote:
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" wrote in message
oups.com...
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]

Code:

---

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


---

Any help? Thanks.