Perl OLE Excel 2003 Problem with freezepane,

[Graig]

On Windows XP, perl 5.10, Excel 2003, Win32 OLE.

No problems with perl creating an Excel spreadsheet, everything seems
to work quite well. The two problem that still remains is:

1) I am trying to freeze pane at row 2:

my $freeze_panes = $gExcel->ActiveSheet->Range("2:2")->Select;
#$gSheet->Cells(2,2)->Select();
$gExcel->ActiveSheet->{FreezePanes} = $TRUE;

I've tried it a couple of ways. After I create and save the excel file
with perl, I can open this new file and I see I am somewhat close, in
that row 2 is highlighted, but the freeze pane has not taken affect.

2) The second problem is that I want to detect if when starting my
script, is Excel already running? What can happen is that the user
abnormally terminates, and leaves a copy of Excel in memory. The only
way to detect this is using MS Task Manager, and kill the excel
process. I'd like my perl script to see if excel is running when it
first comes up, so that I can prompt the user to close excel...


Graig

[A. Sinan Unur]

Post by Graig
On Windows XP, perl 5.10, Excel 2003, Win32 OLE.
No problems with perl creating an Excel spreadsheet, everything seems
my $freeze_panes = $gExcel->ActiveSheet->Range("2:2")->Select;
#$gSheet->Cells(2,2)->Select();
$gExcel->ActiveSheet->{FreezePanes} = $TRUE;

Selecting does not help. You have to tell Excel the row and column where
the split should occur. See below.

Post by Graig
2) The second problem is that I want to detect if when starting my
script, is Excel already running? What can happen is that the user
abnormally terminates, and leaves a copy of Excel in memory. The only
way to detect this is using MS Task Manager, and kill the excel
process. I'd like my perl script to see if excel is running when it
first comes up, so that I can prompt the user to close excel...

Why do you want the user to close Excel? You can instead just get the
already running instance.

#!/usr/bin/perl

use strict;
use warnings;

use File::Spec::Functions qw( catfile );

use Win32::OLE;
use Win32::OLE::Const 'Microsoft Excel';
$Win32::OLE::Warn = 3;

my $excel = get_excel();
$excel->{Visible} = 1;

my $book = $excel->Workbooks->Add;
my $sheet = $book->Worksheets->Add;

$excel->ActiveWindow->{SplitRow} = 0;
$excel->ActiveWindow->{SplitColumn} = 2;
$excel->ActiveWindow->{FreezePanes} = 1;

$book->SaveAs(catfile $ENV{TEMP}, 'test.xls');
$book->Close(0);

sub get_excel {
my $excel;
eval {
$excel = Win32::OLE->GetActiveObject('Excel.Application');
warn "got already active Excel\n";
};

die "$@\n" if $@;

unless(defined $excel) {
$excel = Win32::OLE->new('Excel.Application',
sub { $_[0]->Quit }
) or die "Oops, cannot start Excel: ",
Win32::OLE->LastError, "\n";
}
return $excel;
}

[Graig]

Your example works, but of course the split on the first row causes
the first row to replicate.

What I was trying to do is just freeze the 2nd row, keeping the header
row when scrolling. Perhaps it can not be done programmatically.

Your solution to my 2nd problem was great.

Thanks.

Post by A. Sinan Unur

Post by Graig
On Windows XP, perl 5.10, Excel 2003, Win32 OLE.
No problems with perl creating an Excel spreadsheet, everything seems
  my $freeze_panes = $gExcel->ActiveSheet->Range("2:2")->Select;
  #$gSheet->Cells(2,2)->Select();
  $gExcel->ActiveSheet->{FreezePanes} = $TRUE;

Selecting does not help. You have to tell Excel the row and column where
the split should occur. See below.

Post by Graig
2) The second problem is that I want to detect if when starting my
script, is Excel already running? What can happen is that the user
abnormally terminates, and leaves a copy of Excel in memory. The only
way to detect this is using MS Task Manager, and kill the excel
process. I'd like my perl script to see if excel is running when it
first comes up, so that I can prompt the user to close excel...

Why do you want the user to close Excel? You can instead just get the
already running instance.
#!/usr/bin/perl
use strict;
use warnings;
use File::Spec::Functions qw( catfile );
use Win32::OLE;
use Win32::OLE::Const 'Microsoft Excel';
$Win32::OLE::Warn = 3;
my $excel = get_excel();
$excel->{Visible} = 1;
my $book = $excel->Workbooks->Add;
my $sheet = $book->Worksheets->Add;
$excel->ActiveWindow->{SplitRow} = 0;
$excel->ActiveWindow->{SplitColumn} = 2;
$excel->ActiveWindow->{FreezePanes} = 1;
$book->SaveAs(catfile $ENV{TEMP}, 'test.xls');
$book->Close(0);
sub get_excel {
    my $excel;
    eval {
        $excel = Win32::OLE->GetActiveObject('Excel.Application');
        warn "got already active Excel\n";
    };
    unless(defined $excel) {
        $excel = Win32::OLE->new('Excel.Application',
                                  sub { $_[0]->Quit }
        ) or die "Oops, cannot start Excel: ",
                   Win32::OLE->LastError, "\n";
    }
    return $excel;
}
--
(remove .invalid and reverse each component for email address)
comp.lang.perl.misc guidelines on the WWW:http://www.rehabitation.com/clpmisc/