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

Hi monks. i'm trying to write an application that uses perl to open up excel and then transfers control over to the user until they close excel. i read the tutorial on OLE and excel and that helped, but i'm still having trouble. my code looks like this: 
use strict;
use Win32::OLE qw(in with);
use Win32::OLE::Const 'Microsoft Excel';
use Win32::OLE::Variant;
use Win32::OLE::NLS qw(:LOCALE :DATE);
$Win32::OLE::Warn = 3;

my $excelfile = $ARGV[0];
my $Excel = Win32::OLE->GetActiveObject('Excel.Application')
        || Win32::OLE->new('Excel.Application', 'Quit');

$Excel->{'Visible'} = 1;
$Excel->{DisplayAlerts} = 0;
my $Book = $Excel->Workbooks->Add();
my $Book = $Excel->Workbooks->Open($excelfile);

while(1){}

[download]
this code correctly opens up excel but unfortunately freezes and never refreshes the screen to display the file. what more do I need? thanks, Michael

Replies are listed 'Best First'.
Re: Excel and OLE
by jsprat (Curate) on Sep 18, 2002 at 20:06 UTC
    Two things come to mind:
    1. Excel has its own concept of 'Working directory' - It won't find the file unless it is where Excel thinks it should be
    2. The infinite loop at the end is preventing OLE from shutting Excel down properly
    For #1, convert the filename to its full path: 

    use File::Spec;
my $excelfile = File::Spec->rel2abs($ARGV[0]);

    [download]

    Excel will be able to find and open the file. Take out the 'Quit' parameter to new and get rid of the while (1){}, and Excel will stay open so user can work with the spreadsheet.

    As far as returning control to the perl script after the user is done with Excel, you are on your own ;) It will be difficult to tell when the user is done with the spreadsheet, especially if Excel is already open.

[reply]
[d/l]
[select]
      You should be able to "watch" the excel window using Win32::API, once you get the window handle.

      You could conceivably just check for the existence of the window handle repeatedly in a loop, breaking out of it when the handle is destroyed. Just make sure to stick a sleep statement in there so you don't max out your processor in the loop.
[reply]
        Right you are! The FindWindow API lets you search by window class or exact window title, or you can use EnumWindows and check title bar text for a partial match.

        I was trying to point out the potential pitfalls without being too wordy (or too discouraging - there might be a better way to do it;). The original snippet grabs the existing instance of Excel if it is running. If it was already open and the user closes the document, Excel is still running. Or if the end user forgets to close Excel (or maybe just minimizes it and thinks it is closed) Excel is still running - with the same Window class, maybe even with the same window title.

        I did something like this once in VB and it turned into a major support headache for me.

[reply]
Re: Excel and OLE
by Solo (Deacon) on Sep 18, 2002 at 21:13 UTC
    I think you'll need to use events. Look at the 'Events' heading in the Win32::OLE POD. Unfortunately, it seems Excel doesn't provide a default event interface, so we'll need to dig around to try to find it. I'll experiment as I have time. Until then, you can check out google or a PowerPoint by Dave Roth for in-depth use of Win32::OLE.
    --
    May the Source be with you.

    You said you wanted to be around when I made a mistake; well, this could be it, sweetheart.

[reply]
[d/l]
[select]
      Easier than I thought. We need the AppEvents interface. You should be able to adapt this code to your needs: 
      use warnings;
use strict;

use Win32::OLE qw(EVENTS);
my $Excel = Win32::OLE->new('Excel.Application');
$Excel->{Visible} = 1;
my $Workbook = $Excel->Workbooks->Add;


sub Event {
            my ($Obj,$Event,@Args) = @_;
            print "Event triggered: '$Event'\n";
        }

Win32::OLE->WithEvents($Excel, \&Event, 'AppEvents');

while (1) {
    sleep(1);
    Win32::OLE->SpinMessageLoop;
}

      [download]
      --
      May the Source be with you.

      You said you wanted to be around when I made a mistake; well, this could be it, sweetheart.

[reply]
[d/l]
[select]

Back to Seekers of Perl Wisdom