How to control an already open spreadsheet

[Peter Jamieson]

I am having a look at using Perl to control an Excel spreadsheet:

#!/usr/bin/perl -w
use strict;
use warnings;
use Win32::OLE qw(in with);
use Win32::OLE::Const 'Microsoft Excel';
$Win32::OLE::Warn = 3; # die on errors...

# Get already active Excel application or open new
my $Excel = Win32::OLE->GetActiveObject('Excel.Application')
|| Win32::OLE->new('Excel.Application', 'Quit');

# select and open the Excel file
my $Book = $Excel->Workbooks->Open("C:/Documents and
Settings/mysheet.xls");

# do phunny things with the workbook...

No problems....but:

I read the Win32::OLE docs and elsewhere but could not find how to proceed
if the workbook $Book was already open, rather than opening a new instance
of the workbook.

Any help appreciated!

 

[Brian Helterlilne]

I am having a look at using Perl to control an Excel spreadsheet:

#!/usr/bin/perl -w
use strict;
use warnings;
use Win32::OLE qw(in with);
use Win32::OLE::Const 'Microsoft Excel';
$Win32::OLE::Warn = 3; # die on errors...

# Get already active Excel application or open new
my $Excel = Win32::OLE->GetActiveObject('Excel.Application')
|| Win32::OLE->new('Excel.Application', 'Quit');

# select and open the Excel file
my $Book = $Excel->Workbooks->Open("C:/Documents and
Settings/mysheet.xls");

# do phunny things with the workbook...

No problems....but:

I read the Win32::OLE docs and elsewhere but could not find how to proceed
if the workbook $Book was already open, rather than opening a new instance
of the workbook.

Any help appreciated!

You need to read the Excel macro help (Visual Basic).
The method you are looking for is ActiveWorkbook which has the Name
property which is the filename of the open workbook (if any).
which translates into perl:

my $Book = $Excel->ActiveWorkbook; # may be undefined
my $Name = '';
$Name = $Book->{Name} if defined $Book;
unless ( $Name eq 'C:/.......' ) {
$Book = $Excel->Workbooks->Open("C:/...");

 

[Peter Jamieson]

You need to read the Excel macro help (Visual Basic).
The method you are looking for is ActiveWorkbook which has the Name
property which is the filename of the open workbook (if any).
which translates into perl:

my $Book = $Excel->ActiveWorkbook; # may be undefined
my $Name = '';
$Name = $Book->{Name} if defined $Book;
unless ( $Name eq 'C:/.......' ) {
$Book = $Excel->Workbooks->Open("C:/...");

G'day Brian!
Thank you for your assistance!
Your comments have got me moving forward again on this,
especially your advice re VB and the Name property of the workbook.
Again, many thanks!
Cheers, Peter