Digipede: Distributing Excel Computations – Part 4

Now that I’ve run my calculations I’m ready to return the results to the Master workbook. As I said at the end of Part 3 I’m going to save my results in to a results workbook and send that workbook back to the Master workbook. This means defining another FileDef in my Master workbook and since I want my results in a different directory than my input files, I create a new RemoteLocation as well.

Updates to Excel Master with VBA code-behind: (New code bolded)

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

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

    ' Create a RemoteLocation for the Results files
    Set location = New Digipede_Framework.RemoteLocation
    location.ID = 1
    location.location = fileResultsLoc
    location.ProtocolType = Digipede_Framework.ProtocolType_Share
    jobTemplate.RemoteLocations.AddItem location
.
.
.
    ' Create a filedef for the Data workbook
    Dim dataWBFileDef As Integer
    Dim strInputDataFile As String
    strInputDataFile = "InputDataFile"
    
    Set fileDef = New Digipede_Framework.fileDef
    fileDef.ID = 2
    dataWBFileDef = fileDef.ID
    fileDef.RemoteLocationId = 0
    fileDef.Name = strInputDataFile
    fileDef.Relevance = Digipede_Framework.Relevance_InputPlaceholder
    jobTemplate.FileDefs.AddItem fileDef
    
    ' Create a filedef for the Results workbook
    Dim resultsWBFileDef As Integer
    Dim strResultsDataFile As String
    strResultsDataFile = "ResultsDataFile"
    
    Set fileDef = New Digipede_Framework.fileDef
    fileDef.ID = 3
    resultsWBFileDef = fileDef.ID
    fileDef.RemoteLocationId = 1
    fileDef.Name = strResultsDataFile
    fileDef.Relevance = Digipede_Framework.Relevance.Relevance_ResultPlaceholder
    jobTemplate.FileDefs.AddItem fileDef
.
.
.
    Dim strResultFile As String
    
    ' Create a job
    For i = 1 To 5
        Set oTask = New Digipede_Framework.Task
        
        ' Set up the data file transfer
        strDataFile = "Data" + Application.WorksheetFunction.Text(i, "00") + ".xls"
        
        Set fileDef = New Digipede_Framework.fileDef
        fileDef.ID = dataWBFileDef
        fileDef.RemoteLocationId = 0
        fileDef.LocalName = "Data.xls"
        fileDef.RemoteName = strDataFile
        fileDef.Name = strInputDataFile
        fileDef.Relevance = Digipede_Framework.Relevance_Input
        oTask.FileDefs.AddItem fileDef        

        ' Set up the Results file transfer
        strResultFile = "Result" + Application.WorksheetFunction.Text(i, "00") + ".xls"
        
        Set fileDef = New Digipede_Framework.fileDef
        fileDef.ID = resultsWBFileDef
        fileDef.RemoteLocationId = 1
        fileDef.LocalName = "Result.xls"
        fileDef.RemoteName = strResultFile
        fileDef.Name = strResultsDataFile
        fileDef.Relevance = Digipede_Framework.Relevance_Result
        oTask.FileDefs.AddItem fileDef
        
        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

The changes to the Master are all that is required to move the Results file. But
there are some important issues that need to be addressed in how the Worker saves the Results.xls file.

By default Excel will save a file in the last specified save directory. This means that you must save the Results file using a fully qualified path. Since we set the Application.DefaultFilePath in the RunWorker script we use that to save the Result file in the location the Digipede Network is expecting to find it.

    ResultFileName = Application.DefaultFilePath & "\Result.xls"
.
.
.
    oResultBook.SaveAs Filename:= ResultFileName
    oResultBook.Close False
    Set oResultBook = Nothing

The last thing we need to do is to make sure that the Results directory specified in the Master variable fileResultsLoc allows the Digipede Network to write to it. If your Results directory is not a top level directory make sure that you change the permissions for the top level directory.

In Windows Explorer right-click on the Results directory, select ‘Properties’, then the ‘Sharing’ property page. On the ‘Sharing’ page select the ‘Permission’ button, and on the ‘Permissions for Results’ property page check ‘Full Control’. Select the ‘OK’ button and then the ‘OK’ button again.

We have one last piece to do to finish the development part of this project and that is to pull all the Result files together. In Part 5, I will show you how the Digipede Network notifies the Master application when a Job is complete and how the user can use that notification to process the results.

Essay Links:

Start
Automation
Part 1
Part 2
Part 3
Part 4
Part 5

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