accumulating multiple material costs for a task

[Michael Hansen]

Hello

I originally posted this on the microsoft.public.project discussion, but was
informed that it would be best here.

I want to separate out travel related expenses (material) from service
related costs (work) from software vendors.

Each task will consist of resources mostly of type work. But when travel is
required of those work resources there are multiple material resources
(expenses) to deal with these costs (e.g., flights, taxi, hotel, meals).

I have added a new Travel Cost column and thought I could use the "iff"
statement to extract the cost as a material cost. However, the problem is
that mulitple material resource costs are assigned to the task (e.g., flight,
taxi, hotel, meals) which I want to be able to accumulate into the new Travel
Cost column for the task.

I think I need to be able to loop through the tasks resources (work and
material) and accumulate the costs associated to only the material resources.
Could someone please advise of the best way to deal with this scenario to
accumulate all the material costs associated to a task into the new Travel
Cost column.

I am not overly familiar with VBA, so if someone has a starting point for me
that would be much appreciated.


Regards
Michael

 

[John]

Hello

I originally posted this on the microsoft.public.project discussion, but was
informed that it would be best here.

I want to separate out travel related expenses (material) from service
related costs (work) from software vendors.

Each task will consist of resources mostly of type work. But when travel is
required of those work resources there are multiple material resources
(expenses) to deal with these costs (e.g., flights, taxi, hotel, meals).

I have added a new Travel Cost column and thought I could use the "iff"
statement to extract the cost as a material cost. However, the problem is
that mulitple material resource costs are assigned to the task (e.g., flight,
taxi, hotel, meals) which I want to be able to accumulate into the new Travel
Cost column for the task.

I think I need to be able to loop through the tasks resources (work and
material) and accumulate the costs associated to only the material resources.
Could someone please advise of the best way to deal with this scenario to
accumulate all the material costs associated to a task into the new Travel
Cost column.

I am not overly familiar with VBA, so if someone has a starting point for me
that would be much appreciated.


Regards
Michael

Michael,
Assuming your travel cost column is spare field Cost1 and travel
resources are so identified in the resource name, the following macro
should provide what you want, however, I just threw it together and did
not test it.

Sub travel_cost()
Dim t As Task
Dim a As Assignment
For Each t In ActiveProject.Tasks
If Not t Is Nothing Then
For Each a In t.Assignments
If a.ResourceType = pjResourceTypeMaterial And _
a.ResourceName = "travel" Then
t.Cost1 = a.Cost
End If
Next a
End If
Next t
End Sub

John
Project MVP

 

[Michael Hansen]

Hi John

Thanks for this. This has got me started and almost there.

Instead of using the ResourceName="travel" to extract the travel costs as I
have different names for these material costs, I associate material travel
costs using the Group field as seen in the Resource Sheet.

How do I only process resources where Group="Travel" instead of using the
ResourceName field? Sorry, but I don't yet know the table relationships.

The following is from your sample code with a small change to get the
summation of costs to work.

Private Sub travel_cost()
Dim t As Task
Dim a As Assignment
Dim TravelCost As Long

For Each t In ActiveProject.Tasks
If Not t Is Nothing Then
TravelCost = 0
For Each a In t.Assignments
If a.ResourceType = pjResourceTypeMaterial Then

<How do I process the "Travel" Group (from Resource
Sheet)?>

TravelCost = TravelCost + a.Cost
t.Cost3 = TravelCost
End If
Next a
End If
Next t

End Sub


Regards
Michael Hansen

 

[Jan De Messemaeker]

Hi,

Below =pjresourcetypematerial add
if activeproject.resources(a.resourceid).group="Travel" then
HTH

--
Jan De Messemaeker
Microsoft Project Most Valuable Professional
+32 495 300 620
For availability check:
http://users.online.be/prom-ade/Calendar.pdf

 

[John]

Hi John

Thanks for this. This has got me started and almost there.

Instead of using the ResourceName="travel" to extract the travel costs as I
have different names for these material costs, I associate material travel
costs using the Group field as seen in the Resource Sheet.

How do I only process resources where Group="Travel" instead of using the
ResourceName field? Sorry, but I don't yet know the table relationships.

The following is from your sample code with a small change to get the
summation of costs to work.

Private Sub travel_cost()
Dim t As Task
Dim a As Assignment
Dim TravelCost As Long

For Each t In ActiveProject.Tasks
If Not t Is Nothing Then
TravelCost = 0
For Each a In t.Assignments
If a.ResourceType = pjResourceTypeMaterial Then

<How do I process the "Travel" Group (from Resource
Sheet)?>

TravelCost = TravelCost + a.Cost
t.Cost3 = TravelCost
End If
Next a
End If
Next t

End Sub


Regards
Michael Hansen

Michael,
You're welcome. I'm glad my response got you started in the right
direction. It looks like Jan provided the answer, so unless you have
additional problems/questions, good luck with Project.

John
Project MVP

 

[Michael Hansen]

Thanks Jan and John for your help. I am all sorted now.

Regards
Michael Hansen

 

[John]

Thanks Jan and John for your help. I am all sorted now.

Regards
Michael Hansen

Michael,
Again, you're welcome.
John