PERL Miscellaneous - Disabling auto-calculation in Excel via Win32::OLE

Author

Disabling auto-calculation in Excel via Win32::OLE

James

2006-01-26, 6:59 pm

Can anyone tell me how to disable auto-calculation in Excel (used via
Win32::OLE) please? I've found plenty of references to doing so in
other languages, but for Perl, just some mailing list posts asking the
same thing - and going unanswered, as far as the archive shows...

James.

A. Sinan Unur

2006-01-26, 6:59 pm

"James" <jas88cam@gmail.com> wrote in news:1138284229.788018.114730
@z14g2000cwz.googlegroups.com:

> Can anyone tell me how to disable auto-calculation in Excel (used via
> Win32::OLE) please?

You should use the "Object Browser" in the VBA editor to find out which
objects expose which properties etc.

> I've found plenty of references to doing so in other languages,

It is usually a trivial matter to figure out how to express the same
thing in Perl

> but for Perl, just some mailing list posts asking the

unless one spends all his time searching the web instead of thinking
about it.

In fact, it would have been useful for you to show how it is done in
other languages or at least provide a reference for the code you have
seen so we could be certain we are talking about the same thing.

> same thing - and going unanswered, as far as the archive shows ...

That shall be no more, I hope.

#! /usr/bin/perl

use strict;
use warnings;

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

my $excel;
eval {
$excel = Win32::OLE->GetActiveObject('Excel.Application')
};

die "$@\n" if $@;

unless(defined $excel) {
$excel = Win32::OLE->new(
'Excel.Application',
sub { $_[0]->Quit }
) or die "Oops, cannot start Excel: ", Win32::OLE->LastError, "\n";
}

$excel->Application->{Calculation} = xlCalculationManual;

__END__

--
A. Sinan Unur <1usa@llenroc.ude.invalid>
(reverse each component and remove .invalid for email address)

comp.lang.perl.misc guidelines on the WWW:
http://mail.augustmail.com/~tadmc/c...guidelines.html

James

2006-01-27, 6:59 pm

Well, nice try, but that actually results in an OLE exception, "Unable
to set the Calculation property of the Application class". Perhaps some
of that research you condemn would have helped your code to work?

For anyone who finds this thread in search of a solution to this
problem, the following snippet actually *works*, rather than dying with
an exception:

my $Excel = Win32::OLE->GetActiveObject('Excel.Application')
|| Win32::OLE->new('Excel.Application','Quit');
my $tfile=$Excel->Workbooks->Open("file.xls");
$tfile->Application->{'Calculation'}=xlCalculationManual;

A. Sinan Unur

2006-01-27, 6:59 pm

"James" <jas88cam@gmail.com> wrote in news:1138395485.468910.44520
@f14g2000cwb.googlegroups.com:

> Well, nice try,

What is a nice try? Please quote an appropriate amount of context when
you reply.

> but that actually results in an OLE exception, "Unable
> to set the Calculation property of the Application class".

Curious. I do not get that.

> Perhaps some of that research you condemn would have helped
> your code to work?

Research is when you use the Object Browser or the documentation to find
out about properties you can set and methods you can call. Searching
Google to find a snippet you can lift is something less.

> For anyone who finds this thread in search of a solution to this
> problem, the following snippet actually *works*, rather than dying
> with an exception:
>
> my $Excel = Win32::OLE->GetActiveObject('Excel.Application')
> || Win32::OLE->new('Excel.Application','Quit');
> my $tfile=$Excel->Workbooks->Open("file.xls");
> $tfile->Application->{'Calculation'}=xlCalculationManual;

The code I posted showed which property to set to which value.

It did not give a fatal exception under a variety of conditions. Care to
provide more specific information (such as the actual code you ran, the
version of Excel, Perl, Win32::OLE etc you tried it with)?

Sinan
--
A. Sinan Unur <1usa@llenroc.ude.invalid>
(reverse each component and remove .invalid for email address)

comp.lang.perl.misc guidelines on the WWW:
http://mail.augustmail.com/~tadmc/c...guidelines.html

James

2006-01-27, 6:59 pm

"Research is when you use the Object Browser or the documentation to
find
out about properties you can set and methods you can call. Searching
Google to find a snippet you can lift is something less."

I'd have been delighted if Google had turned up some useful
documentation, but it didn't. It did turn up examples of how to do
precisely what I wanted in half a dozen other languages, but not Perl.
I've been using Perl for years, but always on Unix and never within
driving distance of OLE. Finding good examples of loading and saving
files, accessing cell contents and formatting from Perl raised my
expectations too high. (Given the huge performance difference, it seems
odd the Perl examples I found didn't mention it!)

"It did not give a fatal exception under a variety of conditions. Care
to
provide more specific information (such as the actual code you ran, the
version of Excel, Perl, Win32::OLE etc you tried it with)?"

The code I ran was your earlier post's code, unmodified, with Excel
2003 and Perl 5.8.7 (ActiveState's build 815). It does run without
error if I already have Excel running when the script runs, so the
problem seems to be your invocation of Excel rather than the property
setting itself.

James.

Sponsored Links