Win32::OLE disable calculation

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

Dear Monks,

I am totally new to perl. I have a retro-engineering mission about a software developed long time ago, and only in perl. Why ? Because performance are actually not good . What are we doing with the software ? Extraction from a database to an excel document we are using to make dashboards.

We have found out that the problem of slowness is coming from Excel. Each time we are writing in an excel cell, it calculate all our formula. So my goal here is to disable the automatic calculation of excel with the Win32::OLE library.

So first I have displayed the value I wanted to gave to the Calculation variable :

 my $xl = Win32::OLE::Const->Load("Microsoft Excel");
    printf "Excel type library contains %d constants:\n", scalar keys 
+%$xl;
    foreach my $Key (sort keys %$xl) {
        if ($Key eq "xlCalculationManual" || $Key eq "xlCalculationAut
+omatic" ){
            print "$Key = $xl->{$Key}\n";
        }
    }
[download]

and the result is

Excel type library contains 2177 constants:
xlCalculationAutomatic = -4105
xlCalculationManual = -4135
[download]

This is what I have tried (I can just copy this part of the code but there are other lines) :

use strict;
use Win32::OLE;
use Win32::OLE::Const 'Microsoft Word';
use Win32::OLE::Const 'Microsoft Excel';

my $xl = Win32::OLE::Const->Load("Microsoft Excel");      
my $excel = Win32::OLE->CreateObject('Excel.Application') || die $!;
$excel->{DisplayAlerts}=0;
$excel->{Calculation}=-4135;
[download]

But it doesn't work and I can't understand why... Value of $excel->{Calculation} is -2146826246 and stay the same. I have also tried $excel->{Calculation}=xlManual; or $excel->{Calculation}=xlCalculationManual; but it doesn't work neither.

So if someone could tell me what I am doing wrong... it would be very helpful :). I know I can call a vba macro from perl, but I want to do it only in perl.

Comment on Win32::OLE disable calculation Select or Download Code

Replies are listed 'Best First'.
Re: Win32::OLE disable calculation by davies (Monsignor) on Oct 20, 2015 at 13:41 UTC
Your problem seems to be a function of when, in Excel's boot order, commands are called. `use strict; use warnings; use Win32::OLE; use Data::Dumper; my $xl = Win32::OLE->new('Excel.Application'); $xl->{Visible} = 1; #my $wb = $xl->Workbooks->Add; print Dumper $xl->{Calculation}; $xl->{Calculation}=-4135; print Dumper $xl->{Calculation};` [download] returns `X:\Data\Perl>1145425.pl $VAR1 = bless( do{\(my $o = 40621128)}, 'Win32::OLE::Variant' ); $VAR1 = bless( do{\(my $o = 40621128)}, 'Win32::OLE::Variant' );` [download] But uncomment the line creating the workbook and you get `X:\Data\Perl>1145425.pl $VAR1 = -4105; $VAR1 = -4135;` [download] which is, I think, what you want. Regards, John Davies	[reply] [d/l] [select]
Re^2: Win32::OLE disable calculation by Psylo (Novice) on Oct 20, 2015 at 14:05 UTC
And ... it works ! I was opening the workbook few lines later but as the attribute work on the application I didn't check this... What a dummy error. Thank you a lot !	[reply]