mdamazon has asked for the wisdom of the Perl Monks concerning the following question:

I was able to create a filter on a spreadsheet using this line:

$Sheet->Range("a$filter_row:z$filter_row")->AutoFilter;

Now I would like to sort on the fifth column (column "E") descending. I created a macro in Excel that did just that, but am unable to figure out how to translate it into Perl. The macro looks like this: 

    ActiveWorkbook.Worksheets("A").AutoFilter.Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("A").AutoFilter.Sort.SortFields.Add Key:
+=Range _
        ("E29"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOpti
+on:= _
        xlSortNormal
    With ActiveWorkbook.Worksheets("A").AutoFilter.Sort
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With

[download]
Any suggestions would be appreciated!

Replies are listed 'Best First'.
Re: Win32::OLE and Excel Autofilter
by Corion (Patriarch) on Nov 09, 2015 at 20:26 UTC

    What part do you have problems with?

    Personally, my first step is to always eliminate all Active* mentions and translate them to Perl variables, and also put intermediate parts of a path into variables:

    my $workbook = ActiveWorkbook;
my $sheet = $workbook->Worksheets('A');
my $sort = $sheet->{AutoFilter}->{Sort};
$sort->{SortFields}->Clear();
$sort->{SortFields}->Add(...);
$sort->{Header} = xlYes;
...

    [download]
[reply]
[d/l]
[select]
      Thank you! Your suggestion pointed me in the proper direction. The code i used was:

      $sort->{SortFields}->Clear();
$sort->{SortFields}->Add($Sheet->Range("e$filter_row:e$filter_row"), x
+lSortOnValues, xlDescending, xlSortNormal);
$sort->{Header} = xlYes;
$sort->{MatchCase} = xlYes;
$sort->{Orientation} = xlTopToBottom;
$sort->{SortMethod} = xlPinYin;
$sort->Apply;

      [download]

      All is working as planned.

[reply]
[d/l]

Back to Seekers of Perl Wisdom