There are two approaches to running Excel in HTCondor 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.
HTCondor can be used to run COM automation jobs using the Windows Scripting Host (WSH). This means, for example, HTCondor can run jobs that automate Microsoft Excel. What follows is a step-by-step introduction to running Excel 'jobs' in HTCondor.
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:
- Login to the execute machine as an Administrator.
- Click, Start, click Run and enter dcomcnfg.
- This will bring up the Component Services window.
- Click Console Root, click Component Services, click Computers, and finally, click DCOM Config.
- Right click on the Microsoft Excel Application component and select Properties.
- In the General tab, select None in the Authentication Level drop-down list.
- In the Identity tab, click This user, and enter the name of the administrative account, excel, and its password.
- 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.
NOTE: If your jobs run as specific users on machines and those users are in the Administrators group the DCOM changes are unnecessary. - ichesal
Windows 2008 Server
In addition to the above DCOM changes it appears that jobs that run under the non-visible desktops in Windows 2008 Server (and possibly Win7 and beyond) need some changes to the default environment their given when the desktop is created. Base on this article, you need to create the following directories on your machine to get Excel jobs to function properly.
Windows 2008 Server 64-Bit
Windows 2008 Server 32-Bit
No restart of the machine or HTCondor is required after making the appropriate directory.
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
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"
WScript.Echo "Document Saved"
WScript.Echo "Document Closed"
objExcel.Quit() WScript.Echo "Done" WScript.Exit 0
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
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 Excel that one uses to view the workbook's contents. However, because of security features in newer versions of Office, by default Excel does not automatically execute VBA scripts. A pre-job hook must be executed before to modify a registry key setting to disable the security.
On Vista and higher, regedit cannot be used to modify even the user's own registry tree due to regedit triggering UAC upon invocation. An alternative strategy is to create a simple application that directly writes to the registry key mentioned below.
On XP, a .reg file can be run using regedit through a batch script. The following reg file is needed. Note that each version of Office has this setting in a slightly different place.
Windows Registry Editor Version 5.00 ;Office 2007 [HKEY_CURRENT_USER\Software\Microsoft\Office\12.0\Excel\Security] "VBAWarnings"=dword:00000001
This batch script would serve as the actual pre-job hook.
C:\windows\regedit.exe /s _hookdirectory_/office.reg
Please keep in mind that the bat file must have the absolute path to the .reg file due to the security setup that HTCondor jobs run in, unless the .reg file is sent alongside the job and is in the current execute directory. The full path to regedit is also specified for the same reason, but may not be absolutely necessary.
The embedded VBA is in the actual workbook so instead of calling a script to execute Excel, HTCondor 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.
- Hit Alt+F11 to access the editor for the VBA script.
- 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.
- 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.
- 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 HTCondor if you click Yes.
universe = vanilla executable = excel log = excel.embeddedvba.log.txt error = excel.embeddedvba.err.txt output = excel.embeddedvba.out.txt load_profile = true #Silently start excel without startup screen. arguments = /e filelocation queue 1
Dim ApplicationClass As New AppEventClass
Private Sub Workbook_Open() Set ApplicationClass.Appl = Application End Sub
Public WithEvents Appl As Application
Dim calculating As Boolean Dim answer As VbMsgBoxResult
Private Sub Appl_WorkbookOpen(ByVal Wb As Workbook)
calculating = ActiveWorkbook.ForceFullCalculation
If ActiveWorkbook.ForceFullCalculation = True Then ActiveWorkbook.ForceFullCalculation = False Application.CalculateFull ActiveWorkbook.Save Application.Quit End If
Private Sub Appl_WorkbookBeforeClose(ByVal Wb As Workbook, Cancel As Boolean)
If calculating = False Then answer = MsgBox("Calculate on next open?", vbYesNo, "Calculate") If answer = vbYes Then ActiveWorkbook.ForceFullCalculation = True End If End If