{subsection: Introduction}

There are two approaches to running Excel in Condor jobs, one involving the Windows Scripting Host and the other involving embedded VBA code in the Excel workbooks.  The following are examples for either approach.

{subsection: COM Automation}

Condor can be used to run COM automation jobs using the Windows Scripting Host (WSH).  This means, for example, Condor can run jobs that automate Microsoft Excel.  What follows is a step-by-step introduction to running Excel 'jobs' in Condor.

{subsection: Configuration}

First, you need to create a new administrative user that will be used to run Excel jobs under.  We assume, for convenience, that this account name is *excel*.

To configure the Excel DCOM object, do the following:

1: Login to the execute machine as an *Administrator*.
1: Click, *Start*, click *Run* and enter *dcomcnfg*.
1: This will bring up the *Component Services* window.
1: Click *Console Root*, click *Component Services*, click *Computers*, and finally, click *DCOM Config*.
1: Right click on the *Microsoft Excel Application* component and select *Properties*.
1: In the *General* tab, select *None* in the *Authentication Level* drop-down list.
1: In the *Identity* tab, click *This user*, and enter the name of the administrative account, *excel*, and its password.
1: Click the *OK* button.

*NOTE:* This will render Excel almost inoperable on this machine for any interactive users.  Also, this has only been tested on Vista; the requirements of any future or past versions of Windows may differ slightly.

{subsection: Example}

====

*: =excel.simple.sub=:

  universe     = vanilla
  executable   = excel.simple.vbs
  log          = excel.simple.log.txt
  error        = excel.simple.err.txt
  output       = excel.simple.out.txt
  load_profile = true
  queue 1

====

*: =excel.simple.vbs=:

  On Error Resume Next

  WScript.Echo "Creating Excel Application Object"

  Set objExcel = CreateObject("Excel.Application")
  Set objBooks = objExcel.Workbooks

  WScript.Echo "Creating a Workbook"

  Set objWorkbook = objBooks.Add()

  WScript.Echo "Generating Data"

  For i = 1 to 10
      objExcel.Cells(i, 1).Value = i
  Next

  WScript.Echo "Document Filed"

  Set objFS = CreateObject("Scripting.FileSystemObject")
  sCurPath  = objFS.GetAbsolutePathName(".")
  sFileName = sCurPath & "\" & "test.xlsx"

  objWorkbook.SaveAs(sFileName)

  WScript.Echo "Document Saved"

  objWorkbook.Close()

  WScript.Echo "Document Closed"

  objExcel.Quit()
  WScript.Echo "Done"
  WScript.Exit 0

====

{subsection: Test Run}

Let's take a look at what the script returns. First, submit the job:

  C:\condor-jobs\scripts>condor_submit excel.simple.sub
  Submitting job(s).
  Logging submit event(s).
  1 job(s) submitted to cluster 77.

Once the job has completed, dump the output file:

  C:\condor-jobs\scripts>more excel.simple.out.txt
  Microsoft (R) Windows Script Host Version 5.7
  Copyright (C) Microsoft Corporation. All rights reserved.

  Creating Excel Application Object
  Creating a Workbook
  Generating Data
  Document Filed
  Document Saved
  Document Closed
  Done

{subsection: Embedded VBA}

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 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}

====

*: =excel.embeddedvba.sub=:

  universe     = vanilla
  executable   = excel
  log          = excel.embeddedvba.log.txt
  error        = excel.embeddedvba.err.txt
  output       = excel.embeddedvba.out.txt
  load_profile = true
  arguments    = /e filelocation
  queue 1

====

*: =Embedded VBA Code=:


  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

  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