#! 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);
   }
}