Disabling auto-calculation in Excel via Win32::OLE

J

James

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

A. Sinan Unur

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__
 
J

James

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

A. Sinan Unur

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
 
J

James

"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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Members online

Forum statistics

Threads
473,744
Messages
2,569,479
Members
44,899
Latest member
RodneyMcAu

Latest Threads

Top