Re: Win32::OLE Excel temporary objects destruction

[davies]

I've never felt the need to use Devel::NYTProf and would not claim to understand the technicalities of AUTOLOAD. But the first line of code gets lots of alarm bells ringing with me.

$Excel = Win32::OLE->GetActiveObject('Excel.Application') || Win32::OL
+E->new('Excel.Application', 'Quit');

Even more bells ring when you say that the last line is called tens of thousands of times. Your control structures don't appear, making it look as though this line will be called the same number of times. If so, I guarantee that the code will be painfully slow. This would explain why there is no my in the line I have quoted and might have something to do with your 2:1 ratio. The line I have quoted says (rough, incomplete translation): "however many instances of Excel the user has running, take control of one of them at random, regardless of what it is doing and when it will become available to me". There are times when this is what is meant. They are rare. More usually, my $Excel = Win32::OLE->new('Excel.Application'); will give you what you want - your own, virgin, predictable instance of Excel, without the risk of treading on someone else's work. This is where your code will (not may, WILL) be inefficient. Does an active instance of Excel exist? If so, that instance will need all sorts of tests run on it to determine when control might be passed to Perl. If it's one of my spreadsheets with code that will run for several hours, try coming back tomorrow. If no instance exists, the search through the active processes for one to use is wasted. You can't win. Since there are two possible routes to an active instance of Excel, this may be where your 2:1 ratio is generated.

You will notice that I used my in the line I suggested. It will always be more efficient to create a single instance of Excel and hack that yourself than to create a separate instance for each spreadsheet or whatever. There can be good reasons for using multiple instances. I do it all the time, for example when I want manual recalculation on some files and automatic on others. But I've never needed multiple instances in Perl, so the likelihood is that you will be better with a single instance that you open at the start of your code and close at the end.

I don't know much about the internal workings of Win32::OLE, but in VBA, the docs state that it is faster to specify the target object as directly as possible. So in your line $Book->Worksheets($sheetNumber)->Range("A$Row:E$Row")->{Value} = [[$TimeAbs,$TimeRel,$TimeLog,$Info,$SigValue]];, it MAY (depending on Win32::OLE) be better to create your own array of sheets and then use something like $sht[$sheetNumber]->Range("A$Row:E$Row")->{Value} = ...; instead. And if the row you use is static (which I doubt), you can do even better by assigning the range to your own variable & bypassing the sheet call.

Finally, if you aren't using strict and warnings, they can be very helpful.

Regards,

John Davies