HTCondorWiki: Excel Jobs

 
 For people who wish to dispatch an Excel workbook to another machine for calculation, saving, and then closing of Excel and the return of the workbook, a fairly straightforward solution is to use a VBA script and modifying a setting in the Excel that one uses to view the workbook's contents.
 
-The embedded VBA is in the actual workbook so instead of calling a script to execute Excel, Condor directly calls it.  In this example, the script uses a variable, ForceFullCalculation, for its own purposes and checks if it is true.  If it is, it clears it, saves the workbook, and exits once the recalculations are done.  The next time the user wants to send the workbook out for recalculations, they'll need to set ForceFullCalculation to true again.
+The embedded VBA is in the actual workbook so instead of calling a script to execute Excel, Condor directly calls it.  In this example, the script relies on two settings, Application.Calculation and ActiveWorkbook.ForceFullCalculation.  It checks to see if Calculation is set to xlCalculationAutomatic and ForceFullCalculation is set to True.  If both are true, then it sets ForceFullCalculation to False and Calculation to xlCalculationManual, refreshes and hence forces a recalculation, saves, and then exits the program.
+
+To set the script to do the calculations, follow these steps.  These instructions are for Excel 2007.
+
+Open the Excel workbook where the initial calculation needs to be done.  Open up the menu by clicking on the globe on the upper left and select Excel Options on the lower right of the menu.  Click on the Formulas option on the menu on the left hand side and under Calculation options, change the option to Manual.  You may also wish to uncheck Recalculate workbook before saving.  Save the workbook and close it.  Reopen the workbook and repeat the above steps, except this time select Automatic in the Calculation options.  Save and close.  However, it should be noted that you cannot reopen the workbook again until you have submitted it for processing, as the next time Excel opens the workbook it will go through all the calculations before saving and closing the workbook automatically.
 
 {subsection: Example}
 
@@ -127,24 +131,25 @@
 *: =Embedded VBA Code=:
 
 
-  Option Explicit
-  On Error Resume Next
-
-  Dim notCondor as Boolean
-
-  Private Sub Workbook_Open()
-
-    If ActiveWorkbook.ForceFullCalculation Then
+Public WithEvents Appl As Application
 
+Private Sub Appl_WorkbookOpen(ByVal Wb As Workbook)
+   If Application.Calculation = xlCalculationAutomatic Then
+        If ActiveWorkbook.ForceFullCalculation = True Then
       ActiveWorkbook.ForceFullCalculation = False
       ActiveWorkbook.RefreshAll
+            Application.Calculation = xlCalculationManual
       ActiveWorkbook.Save
       Application.Quit
+        End If
+   End If
+End Sub
 
-    Else
-
-      notCondor = True
+Private Sub Appl_WorkbookBeforeClose(ByVal Wb As Workbook, Cancel As Boolean)
 
+    If Application.Calculation = xlCalculationManual Then
+        MsgBox "Closing Workbook"
+        ActiveWorkbook.ForceFullCalculation = True
     End If
 
-  End Sub
+End Sub