forgedascendant has asked for the wisdom of the Perl Monks concerning the following question:

I am having trouble closing excel when I execute the following code:
#!/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\MathC +ad 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";
What happens is that the excel window (named MathCad Bridge.xls) will not close like I want. Instead the file reads (Mathcad in MathCad Bridge.xls). What I can tell is that the mathcad object is still open from where the macro opened it. The macro is as follows (VBA 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
Am I forgetting something? Any help would be appreciated,

Aaron

Replies are listed 'Best First'.
Re: Win32::OLE Closing Excel
by jrsimmon (Hermit) on May 07, 2009 at 18:35 UTC
    Is the problem that the workbook doesn't close or that the excel application doesn't close? You say you can tell that a certain object is still open -- can you explain how you determined that?

    My first suggestion would be to undef $WB; as shown in the perldocs. $WB->close(); may not behave as you expect it to.

    Also, in my code I use $book->close(0);. If memory serves, the parm is to indicate the workbook should not be saved, but I'd have to track it down again to be sure.

    Update: I should say that I use $book->close(0); and undef $book;.