Checking when Excel editing is done

[esr]

My Perl script is doing some preliminary manipulation of data in an Excel spreadsheet and then opens it up for the user to make additions/changes. I want to detect when the user has finished and then proceed with additional processing. My first attempt was the following:

print "When finished editing, close the file.\n";
sleep 3;
$excel->{Visible} = 1;
while ($excel->ActiveWorkbook) {
    sleep 10;
}

This works only under certain timing-dependent conditions. It works if the user closes the file without making any changes. It also works if changes are made only during the sleep period. However if a cell is being edited when the ActiveWorkbook object is being tested, a message appears which says "The message filter indicated that the application is busy", the test fails, and the while loop is terminated. The same thing happens if one tries to close the file and the ActiveWorkbook object is tested while Excel is waiting for a response to "do you want to save changes?".

Is there a way to test if the "message filter" (whatever that is) is busy? Is there some other way to detect when the user has finished editing and has closed the file?

[Errto]

Hi, esr. The general way of trapping error conditions in Perl, which I assume works in Win32::OLE programming as well, is eval blocks. So I would replace your call to $excel->ActiveWorkbook with a subroutine that looked something like (untested):

sub isWorkbookActive {
    my ($excel) = @_;
    eval {
       return $excel->ActiveWorkbook;
     };
     return 1 if $@ =~ /message filter/;
}

But frankly, you will more likely find a more specific (and less Perl-dependent) solution at the MSDN site (look here for starters). Any sample code you find will probably be in VB, but it should translate easily enough. Also look in the section of Win32::OLE's POD about Office. Update: Apparently the answer is more complex than that. Did you see the reply to one of your recent nodes that pointed to Trapping errors with Win32::OLE?

As a total aside, I must comment, esr, on your choice of username. I probably wasn't the only Monk who thought for a moment that you might be ESR of OSI/Cathedral and the Bazaar/fetchmail fame. It seems rather unlikely, given that the other ESR is neither a Win32 programmer nor an especially great fan of Perl, but it's still amusing. Update: ESR is not an opponent of Perl either, by any means. His full assessment is here.

[esr]

Further experimentation indicates that Win32::OLE works differently and does not set $@, nor does the eval make any difference. But error messages can be obtained by using

$errmsg = Win32::OLE->LastError;

This can be searched for the needed string and then ignored. The message still comes out on the screen for the Perl script window but I found that can be turned off by setting the Warn level to 1 instead of 2. So a code snippet that seems to do the job is:

$warnlevel = $Win32::OLE::Warn;
$Win32::OLE::Warn=1;
$done = 0;
while ($done == 0) {
    if ($excel->ActiveWorkbook) {
        sleep 15;
    } else {
        $errmsg = Win32::OLE->LastError;
        if ($errmsg =~ m/busy/) {
            sleep 15;
        } else {
            $done = 1;
        }
    }
}
$Win32::OLE::Warn=$warnlevel;

[esr]

Thanks. I will play with that idea some. It appears that I may be able to use Win32::OLE->LastError in some way to facilitate this also.

Another thing I want to experiment with is the possibility that the errors that cause the Perl script to terminate may be doing so via a signal of some sort. I plan to experiment with that to see if that is the case and I may be able to avoid termination of the script by trapping the signal, if there is one. In the meantime, I posted a new snippet of code to one of my other nodes which bypassed the errors for that specific case without solving the general problem.

WRT my choice of username, this goes back to my years as a systems programmer with mainframes. At my employer's site every user had a three-character userid which was normally his/her initials. If somebody else already had the same initials the middle initial was changed to something else like x or z. When we galumphed into the Unix world we had more freedom and many users expanded their userids to something longer but I never did. So for the 33+ years I worked there I was known by those initials and it is just natural to try selecting that first as a userid on a new site.