OLE Excel AutoFilter

Do you have a question? Post it now! No Registration Necessary.  Now with pictures!

Threaded View

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

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

use Win32::OLE;
use strict;

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

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

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

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

        $value = $SheetSelect -> Range("B".$row) -> ;

    # set auto fit & filter (display drop down menu list)
    my $autoFit = $SheetSelect->Range("B2:C40");;
       ###This is part where I am having problem.. I want to show only

    ## Save

Re: OLE Excel AutoFilter

Quoted text here. Click to load it

I can filter the specific value such as


This will display the row it has 100 as the value. I just want to
display NonBlanks only but it doesn't work. Any help?


Site Timeline