Velocity Reviews - Computer Hardware Reviews

Velocity Reviews > Newsgroups > Programming > Perl > Perl Misc > WIN32::OLE and Data Analysis Add-in

Reply
Thread Tools

WIN32::OLE and Data Analysis Add-in

 
 
rskirsky@qualcomm.com
Guest
Posts: n/a
 
      12-16-2006
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.

 
Reply With Quote
 
 
 
 
A. Sinan Unur
Guest
Posts: n/a
 
      12-16-2006
http://www.velocityreviews.com/forums/(E-Mail Removed) wrote in news:1166262266.840634.101650
@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__

 
Reply With Quote
 
 
 
 
A. Sinan Unur
Guest
Posts: n/a
 
      12-16-2006
"A. Sinan Unur" <(E-Mail Removed)> wrote in
news:Xns989B9C4E16F8Easu1cornelledu@127.0.0.1:

> # 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
 
Reply With Quote
 
Dr.Ruud
Guest
Posts: n/a
 
      12-17-2006
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;

[...]

--
Affijn, Ruud

"Gewoon is een tijger."

 
Reply With Quote
 
A. Sinan Unur
Guest
Posts: n/a
 
      12-18-2006
"Dr.Ruud" <(E-Mail Removed)> wrote in news:em2agi.d0.1
@news.isolution.nl:

> 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
 
Reply With Quote
 
Dr.Ruud
Guest
Posts: n/a
 
      12-18-2006
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)

--
Affijn, Ruud

"Gewoon is een tijger."
 
Reply With Quote
 
Dr.Ruud
Guest
Posts: n/a
 
      12-18-2006
Dr.Ruud schreef:
> A. Sinan Unur:


>> 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)


awk '!a[$0]++'

--
Affijn, Ruud

"Gewoon is een tijger."
 
Reply With Quote
 
A. Sinan Unur
Guest
Posts: n/a
 
      12-18-2006
"Dr.Ruud" <(E-Mail Removed)> wrote in news:em672r.1do.1
@news.isolution.nl:

> Dr.Ruud schreef:
>> A. Sinan Unur:

>
>>> 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'


Nice

Sinan
 
Reply With Quote
 
Ray
Guest
Posts: n/a
 
      12-19-2006

A. Sinan Unur wrote:
> (E-Mail Removed) wrote in news:1166262266.840634.101650
> @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.
>
> > If my script opens perl itself,

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


 
Reply With Quote
 
 
 
Reply

Thread Tools

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Evaluating static analysis and Dynamic analysis tools for C/C++ ssubbarayan C Programming 5 11-03-2009 12:50 AM
Data Structures and Analysis Multiple Choice review yogi_bear_79 C++ 4 05-20-2008 06:19 PM
data extraction, and analysis of text files mambenanje@gmail.com Java 6 07-07-2006 03:54 AM
Building a Perl based online survey and data gathering, reporting and analysis tool George_V Perl Misc 1 10-05-2004 08:36 AM
input data in asp.net and analysis with pivot table Grey ASP .Net 1 09-24-2004 01:23 PM



Advertisments