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

Two questions about opening Excel workbooks.

The operating system in Windows XP with Perl 5.8.8.
The following Perl code tries to open a spreadsheet and if successful opens a workbook.
$excel = Win32::OLE->new('Excel.Application', 'Quit') or ($excel_res =
+ 0, $ew_message = "Could not create excel bject");

print "[open_spreadsheet] after excel result <$excel_res> mess <$ew_me
+ssage>\n";

if($excel_res == 1) {

    $workbook = $excel->Workbooks->Open($hist_file)  or ($workbook_res
+ = 0, $ew_message = "Could not open excel workbook for <$hist_file>")
+;

    print "[open_spreadsheet] after workbook result <$workbook_res> me
+ss <$ew_message> for <$hist_file>\n";

}

[download]
I have a case where the workbook opening fails although the excel object worked.
To confuse matters this works on my system but not on another one.
I have been told that the spreadsheet was not open in another application such as Excel.
Can any one tell me how to find out what I wrong?
Are there any error codes available that may help?
I am reasonbly certain that the file is there but I will be checking more thoroughly.

On a more general point is there any way I can find out if a spreadsheet is open because it is being used by Excel?
The stat function gives a lot of data but not this bit.

Replies are listed 'Best First'.
Re: Excel opening problems
by strat (Canon) on Jun 23, 2007 at 09:13 UTC

    I usually use something like the following code to open an excel file:

    #! /usr/bin/perl
use warnings;
use strict;

use Win32;
use Win32::OLE;

# the following two may or may not be needed, depending on
# your excel file and your code
use Win32::OLE::Const 'Microsoft Excel';
use Win32::OLE::Variant;

my $excelFile = shift( @ARGV ); # or whatever

defined $excelFile or die "Usage: $0 excelFile\n";
-f $excelFile or die "Error: file '$excelFile' doesn't exist";

# convert to windows-full-path
my $fullFilename = Win32::GetFullPath( $excelFile );

# try to re-use running instance of Excel
my $excel;
eval { $excel = Win32::OLE->GetActiveObject( 'Excel.Application' ) };
if( $@ ) {
    die "Error: no Excel installed: $@\n";
} # if

unless( defined $excel ) { # if not running, start it
    $excel = Win32::OLE->new( 'Excel.Application',
                              sub { $_[0]->Quit } )
        or die "Error: can't start Excel\n";
} # unless

# to see what happens; useful for debugging
$excel->{Visible} = 1;
    
my $workbook = $excel->Workbooks->Open( $fullFilename )
    or die "Error: can't open Workbook '$fullFilename'\n";

...

    [download]
    (not tested)

    Best regards,
    perl -e "s>>*F>e=>y)\*martinF)stronat)=>print,print v8.8.8.32.11.32"

[reply]
[d/l]
      Many thanks for the Perl code. I will try this and see if it helps to open and/or find the problem
[reply]
Re: Excel opening problems
by thezip (Vicar) on Jun 22, 2007 at 17:03 UTC

    By any chance, is the spreadsheet password protected?


    Where do you want *them* to go today?
[reply]
      No - but thanks for the question.
[reply]

Back to Seekers of Perl Wisdom