Beefy Boxes and Bandwidth Generously Provided by pair Networks
laziness, impatience, and hubris

Perl r/w Excel with OLE

by Henry07 (Initiate)
on Jun 28, 2012 at 08:26 UTC ( #978853=perlquestion: print w/replies, xml ) Need Help??
Henry07 has asked for the wisdom of the Perl Monks concerning the following question:

I m using Perl module WIN32::OLE to read and write Excel. It works fine except when I have an error in my Perl code, so the programm does not end but hangs. When I stop the Browser where my Perl programm runs, the OLE instance of Excel is probably not closed. Only turning of the PC and starting again closes this instance (so far I can see).

What other way is there to close my workbook ($Book->Close;) in case of an error in my Perl prog ?

Thanks for any help


Replies are listed 'Best First'.
Re: Perl r/w Excel with OLE
by Corion (Pope) on Jun 28, 2012 at 08:57 UTC

    For conveniently shutting down Excel or the connected workbook, see the "destructor" option to the new() constructor. It will execute a subroutine when your program finishes.

    The hard way of cleaning up short of rebooting is to kill all instances of excel.exe, either manually through the task manager (taskmgr.exe) or with Perl through parsing tasklist.exe output and kill.

      To expand on this, the EXAMPLES section of the Win32::OLE documentation gives a complete walk-through example that shows the quit command for the destructor:
      use Win32::OLE; # use existing instance if Excel is already running eval {$ex = Win32::OLE->GetActiveObject('Excel.Application')}; die "Excel not installed" if $@; unless (defined $ex) { $ex = Win32::OLE->new('Excel.Application', sub {$_[0]->Quit;}) or die "Oops, cannot start Excel"; } # get a new workbook $book = $ex->Workbooks->Add; # write to a particular cell $sheet = $book->Worksheets(1); $sheet->Cells(1,1)->{Value} = "foo"; # write a 2 rows by 3 columns range $sheet->Range("A8:C9")->{Value} = [[ undef, 'Xyzzy', 'Plugh' ], [ 42, 'Perl', 3.1415 ]]; # print "XyzzyPerl" $array = $sheet->Range("A8:C9")->{Value}; for (@$array) { for (@$_) { print defined($_) ? "$_|" : "<undef>|"; } print "\n"; } # save and exit $book->SaveAs( 'test.xls' ); undef $book; undef $ex;
      Note that the visible=1 is great for debugging, but shouldn't be used in a production environment once your script is complete. Note also that unless you do a clean quit, then it's possible to have many instances of Excel running, in which case you should make them exit with Task Manager. For cleanly working scripts using the quit idiom shown above, there should only be Excel running when the script is doing whatever it needs to do.

        The first thing that I personally don't like about the sample code you provided is the attempt to check to see if an instance of Excel is running and to take control of it if there is one. Personally, I prefer my script to open a new instance of Excel so that the script does not interfere with the person/script that is using the other instance. Just a personal preference, but wanted to share my thoughts on that topic.

        The sample code's method for closing Excel works fine for Excel 2003 and I believe Excel 2007 too. However, Excel 2010 introduces a few new wrinkles. First, you can specify what version to save the file as (Excel 2007 and older format, Excel 2010 format, etc.). Also, if the format and file extension don't match, Excel likes to prompt the user about what to do. If you have Excel 2010, just manually try saving a new file as different formats and extensions to see what I'm talking about. Also, you can record a macros to see what the VB code looks like to handle some of these "save as" scenarios and convert that to Perl for your script. Just wanted to mention this information in case the OP (or any future reader) is using Excel 2010 (or newer) and finds the information useful.

        Note that the visible=1 is great for debugging, but shouldn't be used in a production environment once your script is complete.

        Ummm...again I disagree and think that this is a matter of personal preference. Although I typical use the visibility setting as you described, I have a coworker who prefers to leave visible set to 1 so that he can visually watch the script as a method to verify that the script really is running and doing what it is supposed to do. Despite disagreeing with him, I personally am not going to tell someone that they shouldn't be doing that. Basically, just applying the concept of TIMTOWTDI.

        I'm afraid I have to disagree extremely strongly with some of the advice you've given here. Some of the issues are minor, such as always hiding the spreadsheet in production code. What if I want to present the user with the spreadsheet? Usually I hide, but it's definitely horses for courses.

        More serious is taking control of an existing instance. For a one-off script, this can be a useful club to have in your bag, but I've written elsewhere (Re: Manipulating open excel sheet) about why this is a bad idea.

        However, the thing that really terrifies me is that your code not only takes control of one of my instances of Excel (I frequently run several - it can be essential to do this), but then closes it. My work! Where has all my work gone? And there's not even an autosave, as those files are automatically deleted when the creating instance exits normally. Now I'll grant you that it can be useful to loop through all instances of Excel and close them if you know for sure that they are all orphans, but the thought of this getting into production code turns me cold even in this weather.


        John Davies

Re: Perl r/w Excel with OLE
by davies (Parson) on Jun 28, 2012 at 09:37 UTC
    use strict; use warnings; use Win32::OLE; my $xl = Win32::OLE->new('Excel.Application'); $xl->{Visible} = 1;

    The code above is how I start working on Excel using Perl. Notice the last line. This makes the instance of Excel visible so that, should my code crash, I can close it manually. I frequently comment it out once the code is finished, but I find it impossible to develop for Excel without seeing what Excel is doing.

    The other way to get rid of an orphaned instance of Excel is to bring up the task manager, select the application and then instruct the task manager to close it.


    John Davies

      Hello Corion and Davies,

      So I have build in the "visible"
      and killing the process EXCEL.EXE in the task manager works fine.

      Thank you for all the inputs

Re: Perl r/w Excel with OLE
by marto (Bishop) on Jun 28, 2012 at 08:53 UTC

    IIRC $Book->Close; simply closes the active workbook, not the Excel application, try $Excel->Quit(); or double check the documentation.

      Hello marto,

      At the end of my prog there is $Book->Close; this closes it nicely, but it is only executed when my program reaches the end ;-)


Log In?

What's my password?
Create A New User
Node Status?
node history
Node Type: perlquestion [id://978853]
Approved by marto
and the rats come out to play...

How do I use this? | Other CB clients
Other Users?
Others cooling their heels in the Monastery: (7)
As of 2017-01-23 01:32 GMT
Find Nodes?
    Voting Booth?
    Do you watch meteor showers?

    Results (190 votes). Check out past polls.