Win32::OLE Excel question

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

Monks,
I'm using Win32::OLE to do some excel work...
I have a worksheet which i need to sort.
However, I do not want to sort the entire sheet.
I only want to sort columns M & N.
I recorded a macro to see what excel does..here's that code

Columns("M:N").Select
    Selection.Sort Key1:=Range("M2"), Order1:=xlAscending, Header:=xlY
+es, _
        OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, 
+_
        DataOption1:=xlSortNormal
[download]

Here is what I do with my Perl code for it (snippet)

$Range_new1 = $Sheet->Range("m2");
$Range_new2 = $Sheet->Range("n2");
$Excel->Selection->Sort({Key1 => $Range_new1,
            Order1 => xlAscending,
            Key2 => $Range_new2,
            Header=>xlYes,
            OrderCustom=>1,
            MatchCase => False,
            DataOption1 => xlSortNormal});
[download]

However, this does not sort only columns M & N but the whole sheet... What am I missing?
Is there any function to select only columns M & N???
Update::
I wanted to know how to put 'Columns("M:N").Select' in PERL OLE and only sort those 2 columns and leave the other columns as it is.
This is my updated code

$Range_new1 = $Sheet->Range("m:n");
$Range_new2 = $Sheet->Range("n2");
$Excel->Selection->Sort({Key1 => $Range_new1,
                         Order1 => xlAscending,
                         #Key2 => $Range_new2,
                         Header=>xlYes,
                         OrderCustom=>1,
                         MatchCase => False,
                         DataOption1 => xlSortNormal});
[download]

Raghu

Comment on Win32::OLE Excel question Select or Download Code

Replies are listed 'Best First'.
Re: Win32::OLE Excel question by 1Nf3 (Pilgrim) on Jan 05, 2009 at 09:44 UTC
I think you should select the range before using Selection->Sort. Try the following: `$Range_new1 = $Sheet->Range("m:n"); $Range_new2 = $Sheet->Range("n2"); $Sheet->Range('m1:n1000')->Select(); $Excel->Selection->Sort({Key1 => $Range_new1, Order1 => xlAscending, #Key2 => $Range_new2, Header=>xlYes, OrderCustom=>1, MatchCase => False, DataOption1 => xlSortNormal});` [download] It's untested, and it should only work for the first 1000 rows, but if it works, you can always determine your last row number using the method described here: 153486 If I get my hands on Excel, I'll try and see if it works, and post my findings, in the meantime, try this. Regards, Luke	[reply] [d/l]
Re: Win32::OLE Excel question by Corion (Patriarch) on Jan 05, 2009 at 08:45 UTC
Just translate `Columns("M:N").Select` to Perl code, like you did with the rest. Possibly see VBA 2 Perl. Where exactly are you having problems?	[reply] [d/l]
Re^2: Win32::OLE Excel question by imrags (Monk) on Jan 05, 2009 at 09:14 UTC
The problem is that it selects those 2 rows and sorts accordingly but it sorts the whole sheet while doing so which is something i don't want. I only want those 2 columns to be sorted. Raghu	[reply]
Re^3: Win32::OLE Excel question by Corion (Patriarch) on Jan 05, 2009 at 09:32 UTC
In your Perl code, you don't set `$Excel->Selection`. You need to do that, like you do in your VB code.	[reply] [d/l]
Re: Win32::OLE Excel question by imrags (Monk) on Jan 05, 2009 at 09:48 UTC
Thank you so much Corion & 1Nf3. I had forgotten to select those two (I didn't know i could do that)... Now the problem is solved: `$Range_new1 = $Sheet->Range("m:n"); $Sheet->Range("m:n")->Select;` [download] Thanks once again Raghu	[reply] [d/l]
Re^2: Win32::OLE Excel question by subbu_avd (Initiate) on Jan 20, 2009 at 11:35 UTC
I am having similar problems with sort, can you please post some example..? here is my code ================== use OLE; $xlfile = "F:\\PVCS_Checkin\\sava\\New.xls"; # Create OLE object - Excel Application Pointer $xl_app = CreateObject OLE 'Excel.Application' \|\| die $!; $xl_app->{'Visible'} = 1; $workbook = $xl_app->Workbooks->Open($xlfile); $Sheet = $workbook->Worksheets(1); $Range_new1 = $Sheet->Range("A:B"); $Range_new2 = $Sheet->Range("B6"); $Sheet->Range('A2:B6')->Select(); $xl_app->Selection->Sort({Key1 => $Range_new1, Order1 => xlAscending, Key2 => $Range_new2, Header=>xlYes, OrderCustom=>1, MatchCase => False, DataOption1 => xlSortNormal}); $xl_app->ActiveWorkbook->Close(0); $xl_app->Quit(); ===================	[reply]
Re: Win32::OLE Excel question by Anonymous Monk on Jan 05, 2009 at 07:17 UTC
This to perl `Columns("M:N").Select`	[reply] [d/l]