Sorting spread sheets in Perl via OLE Excel

[Grayarea]

Hi all,

I have a Perl app that generates a spreadsheet in Excell using OLE.

I have done quite a lot of interesting things including adding charts etc, but have become stuck tring to sort data on a worksheet.

Here is what I have been playing with;

    my $start = $offset[$k] + $vert_offset;
    my $end = $offset[$k + 1] + ($vert_offset - 2);

    # Sort data ready for charting

    $sheet4->Rows("$start:$end")->Sort("F5");

This is supposed to sort all of the rows selected using column F5 as the sort column.

It appears to do nothing!

I have looked at the object browser and used the VBA macro which generated this;

# sort rows
Rows("5:58").Select
Selection.Sort Key1:=Range("F5"), Order1:=2, Header:=2,
    OrderCustom:=1, MatchCase:=False, Orientation:=1,
    DataOption1:=0

I cannot work out how to implement this!

Any clues would be great :)

Grayarea

20040928 Janitored by Corion: Added code tags

[Grygonos]

Ok, I think I see it, if you look at the example vba code in the help file for sort. they give a reference to the sort cell, not just the cell location i.e.

$sheet->Rows("5:35")->Sort($sheet->Range("F5"));

I haven't tested this .. but this is my best guess.

---

Grygonos

[Grayarea]

You were spot on, that worked as expected
Thanks,
Grayarea.

[Anonymous Monk]

hi, I need to sort my sheet by first column then by 2nd column then by 3rd column. Do you know how I can do this? I know the topic is 13 years old but I really need this :p Thanks by advance :)

[1nickt]

Can you sort the data with Perl before writing to the spreadsheet?