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.

2/27/2006

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

Labels:

1 Comments:

At September 05, 2023 1:37 PM, Anonymous Capsule Wardrobe said...

Nice blog thanks for poosting

 

Post a Comment

<< Home