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

There is no simple right or wrong; it all depends on what you want to do. TIMTOWTDI and all that.

But, for standard use, please x $bignum, pretty please three times with a cherry on top, don't use GetObject or anything else that takes over an existing copy of Excel, Word or anything else. You don't know why I have opened it. It might be running a macro that will not stop for several days. It might become available at a critical point during a process when user input is required but the user is tearing a piece of paper off the end of a roll. It might have all sorts of strange options set because the user is developing something interesting. I could go on, but I'm sure you get the message. Open your own instance of Excel. If that means that two instances are open, that's fine. I do it all the time, and not just for automation purposes. It's built to take it. On the rare occasions that I use Excel 2013, it seems slow to me, too. That may not be a function of automation, let alone the invocation process. There are all sorts of tricks you can play to speed up automation such as preventing screen updating, recalculation, event handlers and the like. But that's an Excel subject rather than a Perl one.

Regards,

John Davies

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

Replies are listed 'Best First'.
Re^2: Win32-OLE: What is the canonical way to open file for editing? by woland99 (Beadle) on Feb 23, 2016 at 16:53 UTC
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?	[reply] [d/l] [select]
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]