Getting at the StandardRate

[Max]

I'm using Jacks VBA to export data to Excel - no problems but if I ask it to
include the Standard Rate for the Resource it only works if a single resource
is assigned to a task. Where multiple resources are assigned, it exports the
Standard Rate for the last assigned resource and applies this rate to all
resources on that task. Can anyone help me with this one?

 

[John]

I'm using Jacks VBA to export data to Excel - no problems but if I ask it to
include the Standard Rate for the Resource it only works if a single resource
is assigned to a task. Where multiple resources are assigned, it exports the
Standard Rate for the last assigned resource and applies this rate to all
resources on that task. Can anyone help me with this one?

Max,
Jack's code loops through each task object and then assignment object of
those tasks. The Standard Rate however is a property of a resource
object. You don't specify exactly how you modified Jack's code to read
the rate information, but one way of getting the information is by
adding the following line of code in the Asgn loop:

resrate = ActiveProject.Resources(Asgn.ResourceName).StandardRate

Hope this helps.
John
Project MVP

 

[Max]

Hi John
I have edited Jacks code as follows -

For Each t In ActiveProject.Tasks
If Not t Is Nothing Then
dwn 1
Set xlCol = xlRow.Offset(0, t.OutlineLevel)
xlCol = t.Name
If t.Summary Then
xlCol.Font.Bold = True
End If

For Each Asgn In t.Assignments
Set xlCol = xlRow.Offset(0, Columns)
xlCol = Asgn.ResourceName
rgt 1

For Each r In t.Resources
xlCol = r.StandardRate

Next r

dwn 1
rgt 1
xlCol = Asgn.Work / 60
rgt 1
xlCol = Asgn.ActualWork / 60
rgt 1
xlCol = Asgn.BaselineCost
rgt 1
xlCol = Asgn.ActualCost
Next Asgn
Tcount = Tcount + 1
End If
Next t

I tried what you suggested, but I'm going wrong somewhere.
Cheers
Max

 

[John]

Hi John
I have edited Jacks code as follows -

For Each t In ActiveProject.Tasks
If Not t Is Nothing Then
dwn 1
Set xlCol = xlRow.Offset(0, t.OutlineLevel)
xlCol = t.Name
If t.Summary Then
xlCol.Font.Bold = True
End If

For Each Asgn In t.Assignments
Set xlCol = xlRow.Offset(0, Columns)
xlCol = Asgn.ResourceName
rgt 1
xlCol = Asgn.Work / 60
rgt 1
xlCol = Asgn.ActualWork / 60
rgt 1
xlCol = Asgn.BaselineCost
rgt 1
xlCol = Asgn.ActualCost
Next Asgn
Tcount = Tcount + 1
End If
Next t

I tried what you suggested, but I'm going wrong somewhere.
Cheers
Max

Max,
OK. First, this is why you mod doesn't do what you want. As I said, the
standard rate is a property of a resource. Although the lines of code
you added do pull out that data, it does so for all resources at each
assignment. So effectively the information you want is being extracted
but it is not being put in a convenient location on the Excel
spreadsheet (i.e. associated with the resource) and for multiple
assignments, each resource's rate is being overwritten by the next
resource's rate. That is why you only see the last rate for each task's
assigned resource. I know, it tough to keep all this stuff straight.

As far as the code I suggested, it does do what you want if structured
into the code appropriately. Try the following:

For Each t In ActiveProject.Tasks
If Not t Is Nothing Then
dwn 1
Set xlCol = xlRow.offset(0, t.OutlineLevel)
xlCol = t.Name
If t.Summary Then
xlCol.Font.Bold = True
End If
For Each Asgn In t.Assignments
dwn 1
Set xlCol = xlRow.offset(0, Columns)
xlCol = Asgn.ResourceName
rgt 1
xlCol = (Asgn.Work / 480) & " Days"
rgt 1
xlCol = (Asgn.ActualWork / 480) & " Days"
rgt 1
xlCol =
ActiveProject.Resources(Asgn.ResourceName).StandardRate
Next Asgn
Tcount = Tcount + 1
End If
Next t

Of course there are several ways to write the code to do what you want
but the above is a very simply mod to Jack's exiting code. What I didn't
do is add an extra header column to identify the rate, but that's a good
exercise for the student (i.e. you).

Hope this helps.
John
Project MVP