Page History
- 2012-Nov-13 15:51 adesmet
- 2012-Aug-13 12:12 ziliang
- 2012-Jun-27 14:20 ichesal
- 2010-Aug-17 13:21 ziliang
- 2009-Jun-19 12:31 ziliang
- 2009-Jun-17 16:25 ziliang
- 2009-Jun-17 16:11 ziliang
- 2009-May-28 16:11 ziliang
- 2009-May-28 13:34 burnett
- 2009-May-22 12:01 ziliang
- 2009-May-01 14:06 burnett
- 2009-May-01 14:05 burnett
- 2009-May-01 12:24 burnett
- 2009-May-01 12:17 burnett
- 2009-May-01 12:14 burnett
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.
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.
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:
- 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.
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
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
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 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.
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
:
Option Explicit On Error Resume Next
Dim notCondor as Boolean
Private Sub Workbook_Open()
If ActiveWorkbook.ForceFullCalculation Then
ActiveWorkbook.ForceFullCalculation = False ActiveWorkbook.RefreshAll ActiveWorkbook.Save Application.Quit
Else
notCondor = True
End If
End Sub