freeze panes in excel without using a module

[mrbluelamp]

I'm trying to freeze panes in excel without using a module, because I
already know that Spreadsheet::WriteExcel will do it for me, so if that
is your answer, no need to respond.

What I have tried and found not to work:

$generalSheet->Range("A2")->Select;
ActiveWindow->{FreezePanes} = True;

 

[A. Sinan Unur]

(e-mail address removed) wrote in @g44g2000cwa.googlegroups.com:

I'm trying to freeze panes in excel without using a module,

Can you show how you deal with Excel documents without using any module?

Sinan

 

[mrbluelamp]

This is a short example of how I do it

use Win32::OLE::Const 'Microsoft Excel';

$Excel = Win32::OLE->new('Excel.Application', 'Quit');

$Excel->{Visible} = 1;

$Book = $Excel->Workbooks->Add;
$generalSheet = $Book->Worksheets(1);
$generalSheet->Cells(1, 3)->{Value} = 'Data';
$LastRow =
($generalSheet->Cells->SpecialCells(xlCellTypeLastCell)->{Row});

foreach $value (@valueArray)
{
$generalSheet->Cells($LastRow, 3)->{Value} = $value;
}

$generalSheet->Columns->AutoFit;
$Book->Save;
$Book->Close;
$Excel->Quit;

 

[Joe Smith]

I'm trying to freeze panes in excel without using a module, because I
already know that Spreadsheet::WriteExcel will do it

Hold it. Spreadsheet::WriteExcel _is_ a module.
How can you ask for a solution that does not use a module when
you are already using a module?
-Joe

 

[A. Sinan Unur]

(e-mail address removed) wrote in @z14g2000cwz.googlegroups.com:

[ When compsing a follow-up, please make sure to quote context. ]

This is a short example of how I do it

use Win32::OLE::Const 'Microsoft Excel';

You are using modules.

I don't think you are using them correctly.

The code you posted is missing:

use strict;
use warnings;

use Win32::OLE;
$Win32::OLE::Warn = 3;

$Excel = Win32::OLE->new('Excel.Application', 'Quit');

See the Win32::OLE documentation for a way of doing this correctly.

foreach $value (@valueArray)

There is no other mention of @valueArray in your code.

You'll need to post sample code that can be compiled and run.

Sinan

 

[mrbluelamp]

Ok, well if "use Win32::OLE::Const 'Microsoft Excel'" means I'm using a
module, then I am then, and would like a solution using that, I am not
using Spreadsheet::WriteExcel, but I am aware that it can be done with
this module real easy like, is what I was trying to say.

 

[mrbluelamp]

I don't actually need to strict and get warnings to make this short
example to work, but added it anyways, this runs on my computer just
fine:

use Win32::OLE::Const 'Microsoft Excel';
use strict;
use warnings;

$Win32::OLE::Warn = 3;

my $Excel = Win32::OLE->new('Excel.Application', 'Quit');

$Excel->{Visible} = 1;

my $Book = $Excel->Workbooks->Add;
my $generalSheet = $Book->Worksheets(1);
$generalSheet->Cells(1, 3)->{Value} = 'Data';
my $LastRow =
($generalSheet->Cells->SpecialCells(xlCellTypeLastCell)->{Row});

my @valueArray = ();

push(@valueArray,"Value1");
push(@valueArray,"Value2");
push(@valueArray,"Value3");
push(@valueArray,"Value4");

foreach my $value (@valueArray)
{
$LastRow++;
$generalSheet->Cells($LastRow, 3)->{Value} = $value;
}

$generalSheet->Columns->AutoFit;
$Book->Close;
$Excel->Quit;

 

[A. Sinan Unur]

(e-mail address removed) wrote in @g49g2000cwa.googlegroups.com:

[ Please quote an appropriate amount of context when you reply. ]

I don't actually need to strict and get warnings

You may think so. However, originally, you posted:

$generalSheet->Range("A2")->Select;
ActiveWindow->{FreezePanes} = True;

Here is what perl has to say about these lines with strictures and
warnings enabled:

D:\Home\asu1\UseNet\clpmisc> bug
Can't use bareword ("ActiveWindow") as a HASH ref while "strict refs" in
use at D:\Home\asu1\UseNet\clpmisc\bug.pl line 38.

D:\Home\asu1\UseNet\clpmisc> bug
Bareword "True" not allowed while "strict subs" in use at D:\Home\asu1
\UseNet\clpmisc\bug.pl line 38.
Execution of D:\Home\asu1\UseNet\clpmisc\bug.pl aborted due to
compilation errors.

#!/usr/bin/perl

use strict;
use warnings;

use Win32::OLE;
use Win32::OLE::Const 'Microsoft Excel';

$Win32::OLE::Warn = 3;

my $Excel;
eval {
$Excel = Win32::OLE->GetActiveObject('Excel.Application')
};
die "Excel not installed" if $@;

unless (defined $Excel) {
$Excel = Win32::OLE->new('Excel.Application', sub { $_[0]->Quit })
or die "Cannot start Excel";
}

$Excel->{Visible} = 1;

my $Book = $Excel->Workbooks->Add;
my $Sheet = $Book->Worksheets(1);
$Sheet->Cells(1, 3)->{Value} = 'Data';
my $LastRow = $Sheet->Cells->SpecialCells(xlCellTypeLastCell)->{Row};

my @valueArray = qw( value1 value2 value3 value4 );

for my $value (@valueArray) {
$LastRow++;
$Sheet->Cells($LastRow, 3)->{Value} = $value;
}

$Sheet->Columns->AutoFit;
$Sheet->Range("A2")->Select;
$Excel->ActiveWindow->{FreezePanes} = 1;

 

[mrbluelamp]

Sweet, thanks Sinan. I never actually knew what the warnings did either
because I guess I never got any warnings with the other script that I
have that actually has that at the top. Also, I like that little
tidbit you showed about seeing if excel was even installed, thanks
again.