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