Greetings Monks,

My question is with regards to running Excel's "Solver" add-in through PERL.
For those who are unaware, the Solver can be used to numerically solve a given problem inside Excel.
In my case, I need to run the Solver add-in multiple times over various elements,
but given that I'm lazy, and I'd rather use PERL than touch Excel, I want to automate the process.

I've simplified the process down to a minimal script, listed below.
Any help you can give in running the add-in feature would be appreciated.

Thanks in advance,
YarDog 

#! C:/Perl/bin/perl -w
 
use strict;
use Win32::OLE qw(in with);
use Win32::OLE::Const 'Microsoft Excel';
$Win32::OLE::Warn = 3; # die one rrors...
use File::DosGlob;
 
# get already active Excel application or open new
my $constant = Win32::OLE::Const->Load('Microsoft Excel');
my $Excel = Win32::OLE->GetActiveObject('Excel.Application')
|| Win32::OLE->new('Excel.Application', 'Quit');
 
# Load Startup Files
loadDefStartup();
loadAltStartup();
 
# Open Excel sheet that will be modified
 
$Excel->{Visible} = 1;
# $Excel->AddIns("Solver Add-in")->{Installed} = 0;
$Excel->AddIns("Solver Add-in")->{Installed} = 1;
# Make sure Solver is installed
my $Book = $Excel->Workbooks->Add();
my $Sheet = $Book->Worksheets(1); # Template sheet
$Sheet->Range("A1")->{Value} = 'Value #1';
$Sheet->Range("B1")->{Value} = 1;
$Sheet->Range("A2")->{Value} = 'Value #2';
$Sheet->Range("B2")->{Value} = 2;
$Sheet->Range("A3")->{Value} = 'Minimize Sum of Squares';
$Sheet->Range("B3")->{Value} = 1;
# This value should become 1.5 after minimizing
$Sheet->Range("A4")->{Value} = 'Sum of Squares';
$Sheet->Range("B4")->{Value} = '=(B1-B3)^2 +( B2-B3)^2';
 
$Book->SolverOk(
  {
   SetCell   =>'$B$4',
   MaxMinVal => 2,
   ValueOf   => 0,
   ByChange  => '$B$3'
  }
        );
$Book->SolverSolve();
 
exit;
 
sub loadDefStartup {
#load startup pathi tems
    my $defStart = $Excel->{'StartupPath'}."\\*.*";
    $defStart =~ s/\"//g; #get rid of dbl-quotes
    $defStart =~ s/\\/\//g; #change dos backslash to unix front slash
    $defStart =~ s/\s/\\ /g; #escape char for spaces in file name
 
    foreach my $file (File::DosGlob::glob($defStart)) {
 print "defstartup load: $file\n";
 my $file_full_name = Win32::GetFullPathName($file);
 my $wkbk = $Excel->{'Workbooks'}->Open($file_full_name);
   }
}
 
sub loadAltStartup {
#load startup pathi tems
    my $altstart = $Excel->{'AltStartupPath'}."\\*.*";
    $altstart =~s /\"//g; #get rid of dbl-quotes
    $altstart =~ s/\\/\//g; #change dos backslash to unix front slash
    $altstart =~ s/\s/\\ /g; #escape char for spaces in file name
 
    foreach my $file (File::DosGlob::glob($altstart)) {
 print "altstartup load: $file\n";
 my $file_full_name = Win32::GetFullPathName($file);
 my $wkbk = $Excel->{'Workbooks'}->Open($file_full_name);
   }
}

[download]
Please pardon me, I thought the code was straightforward, I apologize.
Let me go more into specifics then.
If you comment out the following code section : 
$Book->SolverOk(
  {
   SetCell   =>'$B$4',
   MaxMinVal => 2,
   ValueOf   => 0,
   ByChange  => '$B$3'
  }
        );
$Book->SolverSolve();

[download]
The code runs to completion, meaning it defines the cells appropriately.
However, this set of code is supposed to run the solver add-in, meaning this is the piece of code I am unsure how to code correctly.

The current code structure is how I assumed PERL would interpret running Solver.
The following is the corresponding VBA code used to run solver on the cell.

Sub Macro1()
    SolverOk SetCell:="$B$4", MaxMinVal:=2, ValueOf:="0", ByChange:="$
+B$3"
    SolverSolve
End Sub

[download]
As you can see, I've made the PERL code to be similar to the VBA code, yet it does not run correctly.
Perhaps you have some insight as to why it does not run correctly that I do not.

If there is any more information you require, please feel free to ask.
Hopefully, this new information sheds light on any questions you had previously.

Once again, thank you in advance for any light you can shed on this problem,
YarDog

In reply to Running Excel's Solver via PERL by YarDog

Title:
Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
to format your post, it's "PerlMonks-approved HTML":


  • Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!
  • Titles consisting of a single word are discouraged, and in most cases are disallowed outright.
  • Read Where should I post X? if you're not absolutely sure you're posting in the right place.
  • Please read these before you post! —
  • Posts may use any of the Perl Monks Approved HTML tags:
    a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr
  • You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)
            For:     Use:
    & &amp;
    < &lt;
    > &gt;
    [ &#91;
    ] &#93;
  • Link using PerlMonks shortcuts! What shortcuts can I use for linking?
  • See Writeup Formatting Tips and other pages linked from there for more info.