Software: Excel with a State Machine and My AutomationSecurity Savior

If you’ve been reading my blog you know that I work for Digipede Technologies which is the only .NET based commercial grid computing solution currently available on the market. Grid computing can speed up and scale out computationally intensive and/or parallelizable algorithms. One big area that needs the power of grid computing is Excel spreadsheets. People build simulations in Excel and those simulations can take a long time to run. So I’m trying to figure out how to solve the various Excel problems that can come up. I’ve identified one pattern which is a Master application calling a Worker workbook. The sub pattern I’m currently working on uses a static Worker workbook.

One of the fun things about working on a leading bleeding technology front is the option of making up your own words. So what is a static Worker workbook? First I’ll define a Worker workbook. A Worker workbook is a workbook that is distributed around the grid and executed on the compute nodes. A static Worker workbook is a Worker workbook that does not require any task specific input parameters.

Well, playing around with my static Worker workbook I got myself into a pickle. To automate a static Worker workbook requires 1) The workbook computation, open, and close must be controlled in the Workbook_Open() routine 2) a state machine is needed to control the execution of computation and initiate automatic shutdown. If you don’t have a state machine then the computation and shutdown will happen every time the workbook is opened. Meaning you can’t edit it or extract the results.

My Worker workbook has three states:

1 = Editable; stay open and don’t run the computation
2 = Run; run the computation then close the workbook
3 = Extract; stay open and don't run the computation, data updated

While I was developing the state machine I made a mistake and set my machine to 2 when it should have been 3, which meant that I was unable to get the workbook open to fix the state. To get around this problem I thought I’d be smart and write a VBScript to load the workbook and change my state cell. But the Workbook_Open() kept being executed which caused the workbook to shut down. Then I discovered the Application.AutomationSecurity property which saved me. The AutomationSecurity property allows you to programmatically control whether your macros are executed or not. By default macros in an automated process are trusted, so they are executed. I did not want the macros to run because I needed the workbook to open (remember my macro was automatically shutting down the workbook). By setting the AutomationSecurity property to MsoAutomationSecurityForceDisable (enumeration value 3) I was able to disable Workbook_Open() and change my state cell from the VBScript. Here is the script:

' Create an Excel instance
Dim objExcel
Set objExcel = CreateObject("Excel.Application")

objExcel.DisplayAlerts = False
objExcel.ScreenUpdating = False

Dim objFileSystem
Set objFileSystem = CreateObject("Scripting.FileSystemObject")

Dim strWorkbook
strWorkbook = objFileSystem.GetAbsolutePathname(".") + "\StaticWorker.xls"

' Save the current security setting
Dim objSecuritySave
objSecuritySave = objExcel.AutomationSecurity

' Disable macros
objExcel.AutomationSecurity = 3

' Open the Workbook
Dim objWorkBook
Set objWorkBook = objExcel.Workbooks.Open(strWorkbook)

' Reset the State
objWorkBook.Worksheets("WorkerSheet").Range("F1") = 1
objWorkBook.Save

' Restore the security setting
objExcel.AutomationSecurity = objSecuritySave

' Clean up and shut down
objWorkBook.Close

Set objWorkSheet = Nothing
Set objWorkBook = Nothing

objExcel.ScreenUpdating = True
objExcel.DisplayAlerts = True
Set objExcel = Nothing

(Just for the record this is not a completed example of launching an automated Excel object. There are other recommended calls to disable interface components that I need to add but this did let me get back to work and I thought it might be of interest.)

Technorati tags: Digipede, Grid Computing, Excel, .VBA, .VBS, Microsoft