WIN32::OLE and Data Analysis Add-in

[rskirsky]

I found this old message (no replys) from 2001. I have the same
problem.
I did discover that if Excel is already open (for example, if another
workbook is
already open), then the perl script successfully creates the histogram.
If my script opens perl
itself, then the call to the histogram function does not work (no data
is created). I'm
not running a macro, but am calling the Data Analysis toolpack routine
directly.

I can work around this by using a batch program to open another dummy
Excel spreadsheet
before I call my perl script, but that is very clumsy. Has anyone
figured out what is going on here?

Oh, yeeh, I've manually aborted the script mid-stream and looked at
excel. It says the toolpak is
loaded and available.

Thanks,
Ray

****Message from Donald McGee in 2001 below*******

Has anyone had any success calling the Microsoft Excel Data Analysis
ToolPak Addin via Win32::Ole?


I'm trying to call an Excel macro via the ->Run method. Excel starts
properly, and the macro gets called and runs -- up to the point that
the
macro calls into the Data Analysis Toolpak routines. At that point,
the macro should create a histogram chart, but the chart doesn't get
created and subsequent attempts to access the non-extant chart object
of
course fail (with VBA error 1004).


The macro runs fine when called directly from within Excel. It only
fails when called from perl. I suspect the problem is related to
loading and intializing the addins.


Win32::Ole starts, stops, reads and writes Excel properly. I'm
manually
loading the atbvbaen, funcres and procdb xla modules. The data for the

chart is being provided via a Microsoft Access query, and is returned
properly when called via Win32::Ole.

 

[A. Sinan Unur]

(e-mail address removed) wrote in @l12g2000cwl.googlegroups.com:

I did discover that if Excel is already open (for example, if another
workbook is already open), then the perl script successfully creates
the histogram.

It would have been nice of you to provide a short but complete script
illustrating this and the problem. Please read the posting guidelines.

If my script opens perl itself,

I am not sure what you mean here.

I can work around this by using a batch program to open another dummy
Excel spreadsheet

No need. A simple Google search with the appropriate keywords reveals:

http://support.microsoft.com/kb/270844

The Perl Win32::OLE version of that script is:

#!/usr/bin/perl

# See Microsoft Knowledge Base Article 213489

use strict;
use warnings;

use File::Spec::Functions qw( canonpath catfile );
use FindBin qw( $Bin );

use Win32::OLE qw(in with);
use Win32::OLE::Const 'Microsoft Excel';
$Win32::OLE::Warn = 3;

my $lib_root = catfile( $ENV{ProgramFiles},
'Microsoft Office', qw(OFFICE11 Library Analysis) );

my $xll_path = catfile $lib_root, 'ANALYS32.XLL';
my $xla_path = catfile $lib_root, 'ATPVBAEN.XLA';

my $excel = get_excel();
$excel->{Visible} = 1;
my $book = $excel->Workbooks->Add;
my $sheet = $book->Worksheets(1);
$sheet->{Name} = 'Data';

$excel->AddIns->Add( $xll_path );
$excel->RegisterXLL( 'Analys32.xll' );

my $atp_book = $excel->Workbooks->Open( $xla_path );
$atp_book->RunAutoMacros(1);

# Fill A2:A101 with random data

$sheet->Cells(1, 1)->{Value} = "Observations";
for my $i ( 2.. 101 ) {
$sheet->Cells($i, 1)->{Value} = rand(10);
}

# Fill B2:B11 with bin range

$sheet->Cells(1, 2)->{Value} = "Limits";
for my $i ( 2 .. 11 ) {
$sheet->Cells($i, 2)->{Value} = $i;
}

$excel->Run(qq{ATPVBAEN.XLA!Histogram},
$sheet->Range('A1:A100'),
'Histogram',
$sheet->Range('B1:B10'),
0, # no Pareto
1, # yes cumulative percentage
1, # yes chart output
1, # yes labels
);

sub get_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 sprintf(
"Cannot start Excel: %s\n", Win32::OLE->LastError
);
}
return $excel;
}


__END__

 

[A. Sinan Unur]

# Fill B2:B11 with bin range

$sheet->Cells(1, 2)->{Value} = "Limits";
for my $i ( 2 .. 11 ) {
$sheet->Cells($i, 2)->{Value} = $i;

Ooops!

$sheet->Cells($i, 2)->{Value} = $i - 1;

$excel->Run(qq{ATPVBAEN.XLA!Histogram},
$sheet->Range('A1:A100'),
$sheet->Range('A1:A101'),

'Histogram',
$sheet->Range('B1:B10'),

$sheet->Range('B1:B11'),

Sinan

 

[Dr.Ruud]

A. Sinan Unur schreef:

[nice Excel demo]
my $lib_root = catfile( $ENV{ProgramFiles},
'Microsoft Office', qw(OFFICE11 Library Analysis) );

I had to make that

my $lib_root = catfile( $ENV{ProgramFiles},
'Microsoft o2k-ar', qw(Office Library Analysis) );

Obviously this Office2000 has been installed in a different directory.
But also the next subdirectory was called Office and not OFFICE11
(here).


Alternative:

#!/usr/bin/perl

# See Microsoft Knowledge Base Article 213489

use strict;
use warnings;

use File::Spec::Functions qw( canonpath catfile );
use FindBin qw( $Bin );

use Win32::OLE qw(in with);
use Win32::OLE::Const 'Microsoft Excel';
$Win32::OLE::Warn = 3;

my $excel = get_excel();

my $lib_root = catfile( $excel->LibraryPath, 'Analysis' ); # <--
# print $lib_root, "\n";

my $xll_path = catfile $lib_root, 'ANALYS32.XLL';
my $xla_path = catfile $lib_root, 'ATPVBAEN.XLA';

$excel->{Visible} = 1;

[...]

 

[A. Sinan Unur]

A. Sinan Unur schreef:

[nice Excel demo]

Thanks ;-)

Too bad the OP doesn't seem to be interested any more.

Alternative:
....

my $lib_root = catfile( $excel->LibraryPath, 'Analysis' ); # <--

Thanks for pointing that out. I must have missed that when searching for
methods in the Object Browser.

Sinan

 

[Dr.Ruud]

A. Sinan Unur schreef:

Too bad the OP doesn't seem to be interested any more.

Welcome to usenet.


I came up with a funny un-doubler today:

perl -ne '$_{$_}||=print'

(to remove duplicate lines)

 

[Dr.Ruud]

Dr.Ruud schreef:

A. Sinan Unur:

Welcome to usenet.


I came up with a funny un-doubler today:

perl -ne '$_{$_}||=print'

(to remove duplicate lines)

awk '!a[$0]++'

 

[A. Sinan Unur]

Dr.Ruud schreef:

Nice ;-)

Sinan

 

[Ray]

(e-mail address removed) wrote in @l12g2000cwl.googlegroups.com:


It would have been nice of you to provide a short but complete script
illustrating this and the problem. Please read the posting guidelines.

Sorry, I looked but didn't find posting guidelines.

I am not sure what you mean here.

I meant that if I already had Excel running before I started my script
(working on something
else, like my taxes, maybe) the script ran without a hitch. But if
Excel were not running,
and had to be started by using the OLE new function, instead of the
getActiveObject function, then the call to the histogram add-in failed,
even though the add-ins menu in
Excel showed that the toolpak was loaded and available.

Your recommended additions to specifically lood the Analysis and
Analysis VBA paks
worked like a charm.

Thank You Very much!!

Ray