HTCondorWiki: Excel Jobs

 
 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.
+1: Open the Excel workbook where the initial calculation needs to be done.
+2: Open up the menu by clicking on the globe on the upper left and select Excel Options on the lower right of the menu.
+3: 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.
+4: Hit Alt+F11 to access the editor for the VBA script.
+5: Open up the file for ThisWorkbook by double clicking it in the project window on the left hand side and copy the code listed in the ThisWorkbook subsection below.
+6: Go to the Insert menu and click Class Module.  In the Properties window, change its name to AppEventClass.  If the Properties window is not visible, hit F4.  Open up the AppEventClass file by double clicking on it in the Project window and copy in the code in AppEventClass subsection below.
+4: Save the workbook and close it.  You will be prompted and asked whether the next time the workbook is opened, calculation should take place.  Click Yes if that is your desire and no if you wish to make further modifications later before sending it out for calculation.  However, it should be noted that if you clicked yes, the workbook will calculate and auto-exit the next time you open it, so be certain that you are ready to send it out to Condor if you click Yes.
 
 {subsection: Example}
 
@@ -128,28 +134,44 @@
 
 ====
 
-*: =Embedded VBA Code=:
+{subsubsection: ThisWorkbook}
 
 
+  Dim ApplicationClass As New AppEventClass
+
+  Private Sub Workbook_Open()
+     Set ApplicationClass.Appl = Application
+  End Sub
+
+{subsubsection: AppEventClass}
+
   Public WithEvents Appl As Application
 
+  Dim calculating As Boolean
+  Dim answer As VbMsgBoxResult
+
   Private Sub Appl_WorkbookOpen(ByVal Wb As Workbook)
-     If Application.Calculation = xlCalculationAutomatic Then
+
+      calculating = ActiveWorkbook.ForceFullCalculation
+
         If ActiveWorkbook.ForceFullCalculation = True Then
             ActiveWorkbook.ForceFullCalculation = False
-            ActiveWorkbook.RefreshAll
-            Application.Calculation = xlCalculationManual
+          Application.CalculateFull
             ActiveWorkbook.Save
             Application.Quit
         End If
-     End If
+
   End Sub
 
   Private Sub Appl_WorkbookBeforeClose(ByVal Wb As Workbook, Cancel As Boolean)
 
-      If Application.Calculation = xlCalculationManual Then
-        MsgBox "Closing Workbook"
+      If calculating = False Then
+          answer = MsgBox("Calculate on next open?", vbYesNo, "Calculate")
+          If answer = vbYes Then
         ActiveWorkbook.ForceFullCalculation = True
       End If
+      End If
+
+      ActiveWorkbook.Save
 
   End Sub