#!/usr/ym/util/perl
use Win32::OLE; 

#Perl "C:/documents and settings/aaron.verellen/desktop/trial3.pl"

Win32::OLE->Option(Warn => 2);

#use existing instance if excel is already running
eval {$EX = Win32::OLE->GetActiveObject('Excel.Application')};
die "Excel not installed" if $@;
unless (defined $EX)
{
	$EX = Win32::OLE->new('Excel.Application', sub{$_[0]->Quit;})
	or die "Unable to start Excel";
}

#Open specific worksheet and tab
my $filePath = 'C:\Documents and Settings\aaron.verellen\Desktop\MathCad Bridge.xls';
my $WB = $EX->workbooks->Open($filePath);
my $S = $WB->worksheets("Caculations");
$S->activate();

#Set values on the worksheet
$S->range("A1")->{value} = 16;
$S->range("B1")->{value} = 17;
$S->range("C1")->{value} = 18;

#call macro to process data
$EX->run("Bridge_To_MathCad");

#get the answers from the worksheet and close the excel sheet
$output1 = $S->range("A2")->{value};
$output2 = $S->range("b2")->{value};

#close excel sheet
$WB->close;

#return the values
print "($output1, $output2)\n"; 

##</code><code>##

Sub Bridge_To_MathCad()
    
    Dim outA, outB As Variant
    Dim inX, inY, inZ As Variant
    Dim Value1, Value2 As Variant
    
    'Read in values to be passed from Excel to Mathcad
    inX = ActiveSheet.Range("A1").Value
    inY = ActiveSheet.Range("B1").Value
    inZ = ActiveSheet.Range("c1").Value
    
    'Get a hold of the mathcad object
    Dim MathcadObject As OLEObject
    Set MathcadObject = ActiveSheet.OLEObjects(1)
    MathcadObject.Activate
    
    'Get a hold of the mathcad worksheet
    Set Ws = MathcadObject.Object.Worksheet
    
    'hand the values over to Mathcad, assign them to varables
    'recalculate, and read the results into excal
    Ws.setValue "x", inX
    Ws.setValue "y", inY
    Ws.setValue "z", inZ
    Ws.Recalculate
    
    'Place the result values into the chosen Excel cells
    ActiveSheet.Range("A2").Value = Ws.getvalue("output1")
    ActiveSheet.Range("B2").Value = Ws.getvalue("output2")
    
    'Return to Excel sheet
    ActiveSheet.Select
End Sub