Over a minute to do: use Win32::OLE::Const 'Microsoft Excel';

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

Hello World!

I've been having trouble with using Excel, in that it's extremely slow. I think I've isolated the problem, but don't know whether it's to do with perl, my pc, or what.

This script:...

use Win32::OLE::Const  'Microsoft Excel';
print xlExtractData."\n";
[download]

Takes 75 seconds to execute!

I did this:
perl -D:Prof test1.pl
dprofpp -r tmon.out
and got this result:

Exporter::export has -1 unstacked calls in outer
Exporter::Heavy::heavy_export has 1 unstacked calls in outer
Total Elapsed Time = 74.89209 Seconds
         Real Time = 74.89209 Seconds
Exclusive Times
%Time ExclSec CumulS #Calls sec/call Csec/c  Name
 99.4   74.45 74.454    538   0.1384 0.1384  Win32::OLE::Const::_Typel
+ib
 0.36   0.268 74.722      1   0.2678 74.721  Win32::OLE::Const::_Typel
+ibs
 0.07   0.051  0.051      1   0.0510 0.0510  Win32::OLE::Const::_LoadR
+egTypeLib
 0.05   0.040  0.040      1   0.0400 0.0400  Win32::OLE::Const::_Const
+ants
 0.04   0.030 74.892      1   0.0300 74.892  main::BEGIN
 0.03   0.020  0.050      3   0.0066 0.0165  Win32::OLE::Const::BEGIN
 0.01   0.010  0.010      2   0.0050 0.0050  Config::fetch_string
 0.01   0.010  0.010      2   0.0050 0.0050  DynaLoader::BEGIN
 0.01   0.010  0.010      3   0.0033 0.0033  vars::BEGIN
 0.00   0.000  0.000      1   0.0000 0.0000  Exporter::Heavy::heavy_ex
+port
 0.00       - -0.000      1        -      -  Config::TIEHASH
 0.00       - -0.000      1        -      -  DynaLoader::dl_load_file
 0.00       - -0.000      1        -      -  DynaLoader::dl_install_xs
+ub
 0.00       - -0.000      1        -      -  warnings::BEGIN
 0.00       - -0.000      1        -      -  Config::import
[download]

My system:
XPpro SP1 with 512 Mb memory, an Intel(R) Pentium(R) M processor 1600MHz with a bus speed of 797 MHz, activeperl v5.8.4 build 810.

I'm in well over my head, now. can anyone help me, please?

Thanks,
Spike.

Comment on Over a minute to do: use Win32::OLE::Const 'Microsoft Excel'; Select or Download Code

Replies are listed 'Best First'.
Re: Over a minute to do: use Win32::OLE::Const 'Microsoft Excel'; by guha (Priest) on Sep 09, 2004 at 10:53 UTC
This thread discusses the same / a similar problem. HIH	[reply]
Re: Over a minute to do: use Win32::OLE::Const 'Microsoft Excel'; by iburrell (Chaplain) on Sep 09, 2004 at 23:08 UTC
How long does it take to start Excel on the machine? Because that is what the `use Win32::OLE::Const 'Microsoft Excel';` line is doing. It loads the 'Microsoft Excel' COM component, which is effectively the Excel application. I am guessing it loads some constants out of the library, but the application startup is where the time goes.	[reply] [d/l]
Re^2: Over a minute to do: use Win32::OLE::Const 'Microsoft Excel'; by spikey_wan (Scribe) on Sep 10, 2004 at 12:27 UTC
I'm not doing anything fancy at all in Excel, just running the 2 line script at the top of this thread! As for opening Excel normally through windows, well it opens almost instantaneously.	[reply]
Re: Over a minute to do: use Win32::OLE::Const 'Microsoft Excel'; by terra incognita (Pilgrim) on Sep 09, 2004 at 14:28 UTC
What is in your Excel workbook? How many sheets, columns, and rows? Any special formula's or macros? I do not see an issue with the same version of perl against Office 2003.	[reply]
Re: Over a minute to do: use Win32::OLE::Const 'Microsoft Excel'; by BrowserUk (Patriarch) on Sep 10, 2004 at 12:59 UTC
Looking at your profile output it shows that `Win32::OLE::Const::_Typelib` is being called 538 times. Presumably, once each for 538 imported constants. The pod shows an alternative syntax that gives you back a hash of the constants rather than exporting each as a constant sub. It's possible, though I cannot test it here, that using the alternate syntax mught take less time? From Win32::OLE::Const pod: The first example imports all Excel constants names into the main name +space and prints the value of xlMarkerStyleDot (-4118). use Win32::OLE::Const ('Microsoft Excel 8.0 Object Library'); print "xlMarkerStyleDot = %d\n", xlMarkerStyleDot; The second example returns all Word constants in a hash ref. use Win32::OLE::Const; my $wd = Win32::OLE::Const->Load("Microsoft Word 8.0 Object Librar +y"); foreach my $key (keys %$wd) { printf "$key = %s\n", $wd->{$key}; } printf "wdGreen = %s\n", $wd->{wdGreen}; The last example uses an OLE object to specify the type library: use Win32::OLE; use Win32::OLE::Const; my $Excel = Win32::OLE->new('Excel.Application', 'Quit'); my $xl = Win32::OLE::Const->Load($Excel); [download] It might even be, but again I cannot test this guess, that using the third form above would only import a subset of available constants applicable to excel? Examine what is said, not who speaks. "Efficiency is intelligent laziness." -David Dunham "Think for yourself!" - Abigail "Memory, processor, disk in that order on the hardware side. Algorithm, algorithm, algorithm on the code side." - tachyon	[reply] [d/l] [select]
Re^2: Over a minute to do: use Win32::OLE::Const 'Microsoft Excel'; by spikey_wan (Scribe) on Sep 13, 2004 at 12:49 UTC
After a lot of fiddling about, I have found 2 things that make a difference. 1. Remove the network cable from my PC. Then the 2 line script runs in ~5 seconds. 2. Modify C:\Perl\site\lib\Win32\OLE\Const.pm, and change this line: return unless -f $filename \|\| $filename !~ /^\w:\\.\.(exe\|dll)$/; to this: return unless $filename !~ /^\w:\\.\.(exe\|dll)$/; and the 2 liner runs almost instantaneously. Should I just do the second fix, and not worry about it, or is there a 'proper' way to sort this out? Thanks, Spike.	[reply]
Re^3: Over a minute to do: use Win32::OLE::Const 'Microsoft Excel'; by BrowserUk (Patriarch) on Sep 13, 2004 at 19:51 UTC
... or is there a 'proper' way to sort this out? I'd certainly apply the second method, and then report the problem to ActiveState. Examine what is said, not who speaks. "Efficiency is intelligent laziness." -David Dunham "Think for yourself!" - Abigail "Memory, processor, disk in that order on the hardware side. Algorithm, algorithm, algorithm on the code side." - tachyon	[reply]