Attached is text code to illustrate problems I am having opening Excel workbooks. I am using Office 2007.
The text code tries to open and save two different spreadsheets.
If a spreadsheet is open on the system I frequently get the message
Can't call method "Add" on an undefined value at ….. at line 41.
The code at 41 is
$$ref_workbook = $$ref_excel    -> Workbooks -> Add;

[download]
The following two previous message seem to indicare that theExcel object has been created successfully.
open_spreadsheet_for_excel_write Excel installation test passed
open_spreadsheet_for_excel_write after excel result <1> excel <Win32::OLE=HASH(0x1995540)> message <>
If a spreadsheet is not open, I sometimes get the same error message when trying to open the second spreadsheet.
This code is extracted from an application where I need to write two different spreadsheets.
If I try to do this without closing the application I find that
Opening the first spreadsheet always works
I frequently get the second error message when opening the second spreadsheet.
Can anyone explain how to cure this temperamental behavior?
use strict;
use Win32;
use OLE;
use Win32::OLE::Const "Microsoft Excel";
my ($excel2, $workbook2, $sheet2, $excel_open_res);
my ($excel, $workbook, $sheet, $close_res);
#==================================================================
#
# open spredsheet_for_excel_write
#
# this opens a new spredshert 
#
#==================================================================
sub open_spreadsheet_for_excel_write($$$$) {
my ($ref_excel, $ref_workbook, $ref_sheet, $ref_excel_res) = @_;
my ($ew_obj_message, $err_dialog, $err_ans, $ew_message);
$ew_obj_message = '';
eval { $$ref_excel = Win32::OLE->GetActiveObject( 'Excel.Application' 
+) };
if( $@ ) {
    $ew_obj_message =  "[open_spreadsheet_for_excel_write] Error: no E
+xcel installed - message <$@>\n";
    if(defined($main::mw_Main)) {
        $err_dialog = $main::mw_Main->Dialog(
                -title => "Excel Installation Failure",
                -text  => $ew_obj_message,
                -bitmap => 'error',
                -default_button => 'OK',
                -buttons => ['OK']);
        $err_ans =  $err_dialog->Show();
    }
} else {
    print "[open_spreadsheet_for_excel_write] Excel installation test 
+passed\n";
}
unless( defined $$ref_excel ) { # if not running, start it
    $$ref_excel = Win32::OLE->new('Excel.Application', 'Quit') or ($$r
+ef_excel_res = 0, $ew_message = "Could not create excel object");
} else {
    $$ref_excel_res = 1;
}
print "[open_spreadsheet_for_excel_write] after excel result <$$ref_ex
+cel_res> excel <$$ref_excel> message <$ew_message>\n";
#___ ADD NEW WORKBOOK
$$ref_workbook = $$ref_excel    -> Workbooks -> Add;
$$ref_sheet    = $$ref_workbook -> Worksheets("Sheet1");
$$ref_sheet -> {Name} = "Raw Costs";
print "[open_spreadsheet_for_excel_write] workbook <$$ref_workbook> sh
+eet <$$ref_sheet>\n";
$$ref_sheet                -> Activate;
}
# main #
# open spreadsheet first time
open_spreadsheet_for_excel_write(\$excel, \$workbook, \$sheet, \$excel
+_open_res);
$workbook -> SaveAs ("C:\\exc1.xlsx");
$close_res = $workbook->Close();
print "[main] A close result <$close_res>\n";    
$excel    -> Quit;
# open spreadsheet second time
open_spreadsheet_for_excel_write(\$excel2, \$workbook2, \$sheet2, \$ex
+cel_open_res);
$workbook2 -> SaveAs ("C:\\exc2.xlsx");
$close_res = $workbook2->Close();
print "[main] B close result <$close_res>\n";    
$excel    -> Quit;

[download]

In reply to Open Excel Spreadsheets Problem by merrymonk

Title:
Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
to format your post, it's "PerlMonks-approved HTML":


  • Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!
  • Titles consisting of a single word are discouraged, and in most cases are disallowed outright.
  • Read Where should I post X? if you're not absolutely sure you're posting in the right place.
  • Please read these before you post! —
  • Posts may use any of the Perl Monks Approved HTML tags:
    a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr
  • You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)
            For:     Use:
    & &amp;
    < &lt;
    > &gt;
    [ &#91;
    ] &#93;
  • Link using PerlMonks shortcuts! What shortcuts can I use for linking?
  • See Writeup Formatting Tips and other pages linked from there for more info.