WIN32::OLE and Data Analysis Add-in

Discussion in 'Perl Misc' started by rskirsky@qualcomm.com, Dec 16, 2006.

  1. Guest

    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.
     
    , Dec 16, 2006
    #1
    1. Advertising

  2. 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__
     
    A. Sinan Unur, Dec 16, 2006
    #2
    1. Advertising

  3. "A. Sinan Unur" <> 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
     
    A. Sinan Unur, Dec 16, 2006
    #3
  4. Dr.Ruud Guest

    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."
     
    Dr.Ruud, Dec 17, 2006
    #4
  5. "Dr.Ruud" <> 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
     
    A. Sinan Unur, Dec 18, 2006
    #5
  6. Dr.Ruud Guest

    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."
     
    Dr.Ruud, Dec 18, 2006
    #6
  7. Dr.Ruud Guest

    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."
     
    Dr.Ruud, Dec 18, 2006
    #7
  8. "Dr.Ruud" <> 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
     
    A. Sinan Unur, Dec 18, 2006
    #8
  9. Ray Guest

    A. Sinan Unur wrote:
    > 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
    >
     
    Ray, Dec 19, 2006
    #9
    1. Advertising

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

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Patrick.O.Ige

    Ole ole

    Patrick.O.Ige, Jul 16, 2006, in forum: ASP .Net
    Replies:
    0
    Views:
    503
    Patrick.O.Ige
    Jul 16, 2006
  2. Drew Pihera
    Replies:
    0
    Views:
    651
    Drew Pihera
    Feb 4, 2004
  3. ssubbarayan
    Replies:
    5
    Views:
    2,340
    Dave Hansen
    Nov 3, 2009
  4. Lance Hoffmeyer
    Replies:
    0
    Views:
    248
    Lance Hoffmeyer
    Nov 17, 2003
  5. Thomas Engel
    Replies:
    0
    Views:
    186
    Thomas Engel
    Sep 1, 2005
Loading...

Share This Page