Re^2: Win32-OLE: What is the canonical way to open file for editing?

Thanks John - that is a very sage advice - I actually wrote bunch of wrapper function for some typical tasks (save as CSV, apply changes and save, apply formatting and save, add comments and save) and I constantly struggle with some degree of unpredictibility. Call that are fine if made once fail on subsequent invocation etc.
It is possible that the culprit is reusing of Excel object:

    # use existing instance if Excel is already running
    unless (defined $excel)
    {
        eval 
        {
            $excel = Win32::OLE->GetActiveObject('Excel.Application')
        };
        die "Excel not installed" if $@;
    }
    
    unless (defined $excel) 
    {
        $excel = Win32::OLE->new('Excel.Application', 'Quit')
                or die "Oops, cannot start Excel";
    }
    #to avoid excessive dialogs when saving in non-Excel format
    $excel->{DisplayAlerts} = 0;
[download]

If I understand you correctly I should always use:

my $excel = Win32::OLE->new('Excel.Application', 'Quit')
                or die "Oops, cannot start Excel";
[download]

Is that correct?
I typically add:

    $excel->Quit();
    undef $excel;
[download]

at the end of any function. Shouldn't it take care or Excel objects?

Comment on Re^2: Win32-OLE: What is the canonical way to open file for editing? Select or Download Code

Replies are listed 'Best First'.
Re^3: Win32-OLE: What is the canonical way to open file for editing? by davies (Monsignor) on Feb 24, 2016 at 17:57 UTC
Yes, I think your last two code fragments should work fine. I will admit to being far briefer (and hence possibly less rigorous) myself because I'm an accountant & I've never encountered (at least for the last decade or two) an accountant's machine without Excel. I typically start with `my $xl = Win32::OLE->new('Excel.Application');` and end either with your Quit line or nothing if I want to leave the Excel instance open (for the user to manipulate further, for example). Since the Quit is close to the end of my code, if not at the end, I just let Perl exit and trust Perl & Windows to sort out memory. If starting Excel doesn't work, it's going to crash somehow and I'm not particularly worried about how, since I've never seen it in the wild. I'm far more defensive with my VBA, though! Regards, John Davies	[reply] [d/l]

Replies are listed 'Best First'.

Re^3: Win32-OLE: What is the canonical way to open file for editing?
by davies (Monsignor) on Feb 24, 2016 at 17:57 UTC

Yes, I think your last two code fragments should work fine. I will admit to being far briefer (and hence possibly less rigorous) myself because I'm an accountant & I've never encountered (at least for the last decade or two) an accountant's machine without Excel. I typically start with my $xl = Win32::OLE->new('Excel.Application'); and end either with your Quit line or nothing if I want to leave the Excel instance open (for the user to manipulate further, for example). Since the Quit is close to the end of my code, if not at the end, I just let Perl exit and trust Perl & Windows to sort out memory. If starting Excel doesn't work, it's going to crash somehow and I'm not particularly worried about how, since I've never seen it in the wild. I'm far more defensive with my VBA, though!

Regards,

John Davies

[reply]
[d/l]