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.

In reply to Win32::OLE disable calculation by Psylo

Title:
Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
to format your post, it's "PerlMonks-approved HTML":


  • Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!
  • Titles consisting of a single word are discouraged, and in most cases are disallowed outright.
  • Read Where should I post X? if you're not absolutely sure you're posting in the right place.
  • Please read these before you post! —
  • Posts may use any of the Perl Monks Approved HTML tags:
    a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr
  • You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)
            For:     Use:
    & &amp;
    < &lt;
    > &gt;
    [ &#91;
    ] &#93;
  • Link using PerlMonks shortcuts! What shortcuts can I use for linking?
  • See Writeup Formatting Tips and other pages linked from there for more info.