Trying to export planned resource hours

[Lee]

All,

I have been trying to resolve a problem I have with a piece of code for a
couple of days now and I am beginning to think I cannot see the wood for the
trees!

Explanation
For each of the project plan I create, I am required to produce an Excel
spreadsheet which (amongst other things), needs to display the following: -

Column 1 - Phase Name
Column 2 - Internal Resource Hours (for Internal resources)
Column 3 - New External Resource Hours (for new contractors)
Column 4 - Current External Resource Hours (for existing contractors)
Column 5 - Totals (the total of hours for each phase)

Each of the resources in the project plan has a Custom Field (Text1) which
simply states if they are an Internal, Current External or New External
Resource.

Each task in the plan could have a mix of these resources assigned to it.

All Level 2 tasks are Phases and anything below each respective Level 2 task
(Phase) is considered a task within that phase.

So, I wrote a script to first establish the phases (level 2 tasks) and group
the tasks under it, calling them tasks of that phase (OutlineChildren). Then,
for each task in that group, I created collections of resources through which
I run tests against the Text1 custom field and add up the hours as I go
along, depending upon the type of resource.

When I output this to a CSV file, the hours are all added up incorrectly and
I think that the checks per resource are happening more than once, giving me
incorrect results. However, I cannot see where I am fouling up in the code so
I have posted it here for you all to look at (if you would be so kind of
course!)

Sub getPhasesAndDollars()

Dim item As Object
Dim coll As Tasks, minor As Task, resColl As Resources
Dim fnum As Integer
Dim CurrExt As Long, NewExt As Long, Intern As Long, hrsTot As Long
Dim projName As String
Dim bod As Resource

'get the name of the project
projName = InputBox("Enter the name for this project")

'create a directory with the name of the project and store all files there
MkDir "C:\" & projName
ChDir "C:\" & projName

'create a CSV file with the name of the project to store info
fnum = FreeFile()
Open projName & " - Phases & Dollars Info.csv" For Output As fnum

'write the column titles in the new file
Write #fnum, "Phase", , "Internal", "New External", "Current External",
"Total"

For Each item In ActiveProject.Tasks 'for each Phase

'Set the hours to zero
CurrExt = 0
NewExt = 0
Intern = 0

'check if the task is a Level 2 Task (Phase)
If item.OutlineLevel = 2 Then
'if the task is Level 2, create a collection of its children
tasks
Set coll = item.OutlineChildren

'Run through each of the tasks in the collection
For Each minor In coll
'For each task, create a collection of it's assigned
resources
Set resColl = minor.Resources
'For each resource in the collection, check the
'resource's Employment Type. For each type,
'add up the hours of work assigned
For Each bod In resColl
If bod.Text1 = "Current External" Then
CurrExt = CurrExt + bod.Work
ElseIf bod.Text1 = "New External" Then
NewExt = NewExt + bod.Work
ElseIf bod.Text1 = "Internal" Then
Intern = Intern + bod.Work
End If
Next bod

hrsTot = hrsTot + CurrExt + NewExt + Intern
Next minor

'convert work to hours
CurrExt = CurrExt / 60
NewExt = NewExt / 60
Intern = Intern / 60
hrsTot = hrsTot / 60

'Write the results for this Phase into the outputted file
Write #fnum, UCase(item.Name), , Intern, NewExt,
CurrExt, hrsTot
End If
Next item

Close fnum

MsgBox ("Phases & Dollars information has now been exported. Check in
C:\" & projName & "\ for the file(s)")

End Sub




Any help with this would be greatly appreciated as this is driving me cray!

Kindest Regards

 

[Lee]

Just to update, I know eher the issue is. Each time my script iterates
through the resources for each task, it is using the total hours the resource
is planned to work for the project and not the hours planned for the resource
to work on that particular task. I think this is coming from the "bod.work"
bit of my iterations.

What is the extension required to output the assigned hours for a particular
resource on a particular task??

 

[Jan De Messemaeker]

Hi Lee,

That object is called an assignment.
There is no such thing as resources on a task or tasks on a resource; a task
has assignments and resources have (the same) assignments.
Example of a snippet:

For each Assn in thistask.assignments
if activeproject.resources(assn.resourceid).group="Office Worker" then
Totalwork=Totalwork+assn.work

etc.
HTH

 

[Lee]

Jan,

You are a star! This was exactly the problem!

Thanks for your words of wisdom!

Regards

Lee