A Day in the Life

A day in my life. Thoughts on leadership, management, startups, technology, software, concurrent development, etc... Basically the stuff I think about from 10am to 6pm.


Digipede: Distributing Excel Computations – Part I

"Yes Virginia, you can run an Excel spreadsheet on a compute grid." It’s true. But you can’t run just any spreadsheet and there is no magic bullet. You will have to do a little work. Something, a Master, has to initiate the work and something, a Worker, has to do the work. Whether the initiator is an Excel spreadsheet or a custom application doesn’t matter, whether the work is an Excel spreadsheet, a DLL, or an executable doesn’t matter. All that matters is the relationship between the Master and the Worker.

For this code sample I’m going to use an Excel Master and I’m going to distribute an Excel Worker. I’ve created a closed system on my machine (I have a Digipede Server, Agent, and Framework all installed on one machine) to simplify my testing environment. Remember when building a distributed application to create your simplest starting case first and then to expand as you verify each phase.

The first thing to do is make sure that I correctly tell the Digipede Network what files to move and how to initiate the Worker spreadsheet. I then will test everything on the compute node.

Excel Master with VBA code-behind:
Private Sub btnStartCalculations_Click()

' Perform validation – we need a valid user id, password and Digipede host address
Dim errMsg As Variant
Dim userId As String
Dim password As String

userId = Sheet1.Range("UserId")
password = Sheet1.Range("Password")
If userId = "" Or password = "" Then
errMsg = "Enter a Digipede Network user id and password."
MsgBox errMsg
Exit Sub
End If

Dim hostName As String
hostName = Sheet1.Range("HostName")
If hostName = "" Then
errMsg = "Enter a Digipede Server name."
MsgBox errMsg
Exit Sub
End If

Dim fileShareLoc As String
fileShareLoc = Sheet1.Range("FileShareLocation")
If fileShareLoc = "" Then
errMsg = "Enter the file share location of the files to distribute."
MsgBox errMsg
Exit Sub
End If

' Depending on the version of the Digipede Network
' you may want to setup pools
Dim poolId As Long
poolId = Sheet1.Range("PoolId")
If poolId = 0 Then
poolId = 1
End If

Dim strLaunchScript As String
strLaunchScript = "RunWorker.vbs"

' Disable the Calculate button
'btnStartCalculations.Enabled = False
Sheet1.Range("SubmissionTime") = ""
Sheet1.Range("FinishedTime") = ""
Sheet1.Range("JobId") = ""

' Initialize the client
ThisWorkbook.mClient.SetCredentials userId, password
ThisWorkbook.mClient.SetUrlFromHost hostName

' Create the JobTemplate
Dim jobTemplate As Digipede_Framework.jobTemplate
Set jobTemplate = New Digipede_Framework.jobTemplate

' I set DiscardAfterUser to False because I’m debugging,
' I plan to move some files to the compute nodes and I
' need to manually confirm that after the move everything
' will work the way I expect.
jobTemplate.DiscardAfterUse = False

' Create a RemoteLocation for the Worker Script
Dim location As Digipede_Framework.RemoteLocation
Set location = New Digipede_Framework.RemoteLocation

location.ID = 0
location.location = fileShareLoc
location.ProtocolType = Digipede_Framework.ProtocolType_Share
jobTemplate.RemoteLocations.AddItem location

' Create a filedef for the launch script – the filedef
' defines a file to be moved by the Digipede Network.
Dim fileDef As Digipede_Framework.fileDef
Set fileDef = New Digipede_Framework.fileDef
fileDef.ID = 0
fileDef.remoteLocationId = 0
fileDef.LocalName = " RunWorker.vbs"
fileDef.Name = " RunWorker.vbs"
fileDef.Relevance = Digipede_Framework.Relevance_JobTemplate
jobTemplate.FileDefs.AddItem fileDef

' Create a filedef for the Worker Excel file
Set fileDef = New Digipede_Framework.fileDef
fileDef.ID = 1
fileDef.remoteLocationId = 0
fileDef.LocalName = "Worker.xls"
fileDef.Name = "Worker.xls"
fileDef.Relevance = Digipede_Framework.Relevance_JobTemplate
jobTemplate.FileDefs.AddItem fileDef

' Create one parameter placeholder
Dim parameter As Digipede_Framework.parameter
Set parameter = New Digipede_Framework.parameter
parameter.Name = "InputText"
parameter.Relevance = Digipede_Framework.Relevance_InputPlaceholder
jobTemplate.Parameters.AddItem parameter

jobTemplate.Control.CommandLine = "cscript RunWorker.vbs $(InputText)"
jobTemplate.Control.SaveStandardError = False
jobTemplate.Control.SaveStandardOutput = False
jobTemplate.Control.UseShellExecute = True

jobTemplate.Name = "ExcelSubmitterTest1"

' now the JobTemplate is complete; create a job with tasks.
Set mJob = New Digipede_Framework.job
mJob.Name = "ExcelTest1"

Dim i As Integer
Dim oTask As Task

' Create a job but only put one Task in. I want to make
' sure that everything works for one Task before I
' add more.
For i = 1 To 1
Set oTask = New Digipede_Framework.Task

' Later I’m going to passing along an input data file.
' Right now I’m just passing in a parameter that will
' eventually be used to build the name of that file.
Dim taskParameter As Digipede_Framework.parameter
Set taskParameter = New Digipede_Framework.parameter
taskParameter.Name = parameter.Name
taskParameter.Value = i
oTask.Parameters.AddItem taskParameter

mJob.Tasks.AddItem oTask
Next i

Sheet1.Range("SubmissionTime") = Format(Time, "hh:mm:ss")

ThisWorkbook.mClient.SubmitJob poolId, jobTemplate, mJob,

Sheet1.Range("JobId") = mJob.JobId

End Sub

Notice that I am distributing a VBS script. I have yet to figure out how to get
Excel to close properly on a remote machine without a script. Code that opens and closes Excel on my machine doesn’t work from a remote call. So for now I invoke the Worker using a script. This actually works fine for this use case because I need to initialize the Worker spreadsheet anyway.

Launch Script to launch the Worker spreadsheet:
' Create a WshShell to get the current directory
Dim WshShell
Set WshShell = CreateObject("WScript.Shell")

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

‘ Disable as much UI as possible.
myExcelWorker.DisplayAlerts = False
myExcelWorker.AskToUpdateLinks = False
myExcelWorker.AlertBeforeOverwriting = False
myExcelWorker.FeatureInstall = msoFeatureInstallNone

' Set this so that the Excel files can be found and saved
Dim strPath
strPath = WshShell.CurrentDirectory
myExcelWorker.DefaultFilePath = strPath

Dim objArgs
Set objArgs = WScript.Arguments

' Open the Workbook specified on the command-line
Dim WBook
Dim strWorkerWB
strWorkerWB = strPath & "\Worker.xls"
Set WBook = myExcelWorker.Workbooks.Open(strWorkerWB)

' This is my test code, I want to update a cell so that I know that I
' opened the spreadsheet and did something.
Dim TaskId
TaskId = objArgs(0)

WBook.Sheets("Automate").Range("O3") = TaskId


' Clean up and shut down
Set WBook = Nothing
Set objArgs = Nothing
Set myExcelWorker = Nothing
Set WshShell = Nothing

Notice in the code that I build a path to the Worker workbook. I need to do this
because of how Excel works, if I don’t use a path then Excel looks in the wrong directory and doesn’t open my workbook.

It should also be noted that neither the Worker workbook nor the Launch script should require user interaction. Anything that runs on a compute node will not complete execution if user feedback is required or message boxes pop up. So make sure you have removed all UI elements.

Test it:

We’re now ready to test. As I stated earlier I run all initial testing on a closed system. This just makes it easier for me to access everything I need. Here are my testing steps:

1. I open the Master Excel spreadsheet and press ‘Start’. This action causes the files I need to be copied to my compute node. In a closed system this results in the files being placed in a specific subdirectory on my machine.
2. Once the Master returns I open Digipede Control and get my JobId for that test.
3. I then open a Command Prompt and go to the directory: “C:\Documents and Settings\All Users\Application Data\Digipede\Agent\Data”. The subdirectory I want from here is JobId plus “v1”. So in this case I will go to “15v1” because my JobId is 15.
4. I look in the subdirectory to see if the files I was expecting are there. Yep, looks good.

The code above is the working code but it didn’t work in its first incarnation. Debugging was required. To debug what is happening on the compute node, I ran the RunWorker.vbs file from the command-line with the following call:

cscript /x runworker.vbs 1

I then modified the script until it worked. When I had a working script I replaced the original copy of RunWorker.vbs with the working copy.

I now know that the files are being moved and that my script is launching and initializing the Worker correctly. The next step will be to add an input data file and I will cover that in Part 2.

Essay Links:

Part 1
Part 2
Part 3
Part 4
Part 5



Post a Comment

Links to this post:

Create a Link

<< Home