Click here to get back home

OLE Excel AutoFilter
 Home | NewsGroups | Search | About
Login Password
Register Now!
 comp.lang.perl.modules    Post an article   get this group's latest topics as an RSS feed add this group's latest topics to your My MSN content add this group's latest topics to your My Yahoo content
Subject Author Date
OLE Excel AutoFilter Kenny 09-10-2006
Posted by Kenny on September 10, 2006, 4:28 pm
Please log in for more thread options

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");
        $SheetSelect->Select;
        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;

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

        # 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("B:C","(NonBlanks)");


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


Posted by Kenny on September 10, 2006, 5:13 pm
Please log in for more thread options


> ###This is part where I am having problem.. I want to show only
> NonBlanks
>         $autoFit->Columns()->AutoFilter("B:C","(NonBlanks)");
>


I can filter the specific value such as

$autoFit->Columns()->AutoFilter("1","100");

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?

$autoFit->Columns()->AutoFilter("1","NonBlanks");
$autoFit->Columns()->AutoFilter("1","(NonBlanks)");


Similar ThreadsPosted
Help Perl and Excel August 16, 2006, 9:25 am
Perl and Excel April 6, 2007, 1:06 am
Excel Data conversion July 27, 2004, 2:37 pm
Problem with Name of Excel Chart September 22, 2005, 9:21 am
DBD:Excel on unix machine March 9, 2006, 11:18 am
Everything but the column: win32::OLE Excel fun June 7, 2006, 9:07 pm
Writing row at a time in Excel using OLE June 14, 2007, 1:43 pm
How to write into existing Excel Spreadsheets? July 13, 2004, 1:24 pm
Can I write Macros in excel workbook ? October 8, 2004, 7:50 am
Reading AND writing Excel spreadsheets April 30, 2005, 10:05 am

Our other projects:

Art Dolls, Fairies and Mermaids - Sunnyfaces.net

Roy's Linux, Programming and Search Engines messages

1-Script XML SitemapXML Sitemap