Report reosurce cost and utilization by month into VB

Z

zzqv9p

I want to extract project data into excel. I can extract basic information:
project start & finish date, task names, resource names associated with tasks.

I am trying to build 2 matrices

resource name X YYYY-MMM to contain either effort or cost

when I try to extract cost by resource I get the cost of the resource for
all tasks. can someone direct me on how I can extract into VB the effort &
cost for each resource by date?
I started with this program to dump the contents, but I cannot figure out
which variables will give me the actual days that will be worked on and the
cost
Sheets("TEMP").Select
Range("A2").Select
ActiveProject.Activate
hours_Per_Day = ActiveProject.HoursPerDay
Dim t As Task
Dim ts As tasks
Dim A As Assignment
Set ts = ActiveProject.tasks
For Each t In ts
If Not t Is Nothing Then
' For Each A In t.Assignments
'change the following line to use
'for a different custom field

'rid = A.ResourceID
start_date = t.Start
end_date = t.Finish
num_resources = 0
task_name = t.Name
duration = t.duration
task_cost = t.Cost
For j = 1 To t.Resources.Count
ActiveCell.Offset(0, 0) = task_name
ActiveCell.Offset(0, 1) = start_date
ActiveCell.Offset(0, 2) = end_date
ActiveCell.Offset(0, 3) = t.Resources(j).PeakUnits
ActiveCell.Offset(0, 5) = duration / 60 / hours_Per_Day
ActiveCell.Offset(0, 6) = t.Resources(j).Name
ActiveCell.Offset(0, 7) = t.Resources(j).Cost
ActiveCell.Offset(0, 8) = t.Resources(j).Number5
ActiveCell.Offset(0, 9) = t.Resources(j).Code
ActiveCell.Offset(0, 10) = t.Resources(j).StandardRate
summ_row = summ_row + 1
Range("A" & summ_row).Select
Next
End If
Next t
 
J

Jan De Messemaeker

Hi,

Work and Cost per time unit can be read via the Timescaledata method into
timescalevalues variables.
Look for help on these (it's several pages, can't just summarize that here)

And why do you avoid assignments? They are the normal way to see what is teh
work on a task by each resource.
Hope this helps,

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

zzqv9p

thanks I found some references to timescaledata.
I wrote this piece of code but I cannot find a reference to the units used
by value
when I run the code below I get a value of 11040
I know the resource is needed for 10 days for 1 month
I have 8 hours per day defined
do you know what the unit is of this value?




ActiveProject.Activate
Dim tsvs As TimeScaleValues
Set tsvs = ActiveProject.Resources("Enterprise
Architect").TimeScaleData(StartDate:=ActiveProject.ProjectStart,
EndDate:=ActiveProject.ProjectFinish, TimeScaleUnit:=pjTimescaleMonths,
Count:=1)
MsgBox tsvs.Count

For TSV = 1 To tsvs.Count



If Not IsNull(tsvs(TSV).Value) And Not tsvs(TSV).Value = "" And
Val(tsvs(TSV).Value) <> 0 Then

MsgBox (tsvs(TSV).Value / 60 / ActiveProject.HoursPerDay)
MsgBox tsvs(TSV).Value
End If

Next
 
R

Rod Gill

Hi,

You are missing the Type parameter that defines what data is returned

expression .TimeScaleData(StartDate, EndDate, Type, TimeScaleUnit, Count)

Note that work is returned as a number of minutes. The value for each
timescale object is "" for time slices with no data, so I usually use
val(tsv.value) so a zero is returned in those cases, otherwise test for it.

--

Rod Gill
Microsoft MVP for Project

Author of the only book on Project VBA, see:
http://www.projectvbabook.com
 
Z

zzqv9p

thanks, I am getting a lot closer. I entered my standard rate as $00.00/month
for a monthly rate. when I extract it from the code below I get it /hr for
hourly. Is there any way to get the rate to be extracted as the way it was
entered, or to allow me to select the frequency?

Here is the code I am using that dumps the resources, effort, month, in
case someone is trying to do something similar and trying to place the effort
into excel


Sheets("TEMP").Select
Range("A20").Select
summ_row = 20
Dim tsvs As TimeScaleValues
Set r = ActiveProject.Resources
ActiveProject.Activate
total_resources = ActiveProject.Resources.Count
For i = 1 To total_resources
Set tsvs =
ActiveProject.Resources(r(i).Name).TimeScaleData(StartDate:=ActiveProject.ProjectStart,
EndDate:=ActiveProject.ProjectFinish, TimeScaleUnit:=pjTimescaleMonths,
Count:=1)
If tsvs.Count > 0 Then

For TSV = 1 To tsvs.Count
If Not IsNull(tsvs(TSV).Value) And Not tsvs(TSV).Value = "" And
Val(tsvs(TSV).Value) <> 0 Then
ActiveCell.Offset(0, 6) = (tsvs(TSV).Value / 60 /
ActiveProject.HoursPerDay / r(i).MaxUnits)
ActiveCell.Offset(0, 1) = r(i).StandardRate
ActiveCell.Offset(0, 2) = r(i).PeakUnits
ActiveCell.Offset(0, 3) = r(i).Number5
ActiveCell.Offset(0, 4) = r(i).Code
ActiveCell.Offset(0, 0) = r(i).Name
ActiveCell.Offset(0, 5) = TSV
summ_row = summ_row + 1
Range("A" & summ_row).Select
End If
Next

End If
Next
 
Z

zzqv9p

I should have done a bit more investigating before doing the last post. I
found what I am looking for using the TYPE:= option.

Sheets("TEMP").Select
Range("A20").Select
summ_row = 20
Dim tsvs As TimeScaleValues
Set r = ActiveProject.Resources
ActiveProject.Activate
total_resources = ActiveProject.Resources.Count
For i = 1 To total_resources
Set tsvs =
ActiveProject.Resources(r(i).Name).TimeScaleData(StartDate:=ActiveProject.ProjectStart,
EndDate:=ActiveProject.ProjectFinish, TimeScaleUnit:=pjTimescaleMonths,
Count:=1)
Set tcost =
ActiveProject.Resources(r(i).Name).TimeScaleData(StartDate:=ActiveProject.ProjectStart,
EndDate:=ActiveProject.ProjectFinish, Type:=pjResourceTimescaledCost,
TimeScaleUnit:=pjTimescaleMonths, Count:=1)
If tsvs.Count > 0 Then

For TSV = 1 To tsvs.Count
If Not IsNull(tsvs(TSV).Value) And Not tsvs(TSV).Value = "" And
Val(tsvs(TSV).Value) <> 0 Then
ActiveCell.Offset(0, 6) = (tsvs(TSV).Value / 60 /
ActiveProject.HoursPerDay / r(i).MaxUnits)
ActiveCell.Offset(0, 1) = r(i).StandardRate
ActiveCell.Offset(0, 2) = r(i).PeakUnits
ActiveCell.Offset(0, 3) = r(i).Number5
ActiveCell.Offset(0, 4) = r(i).Code
ActiveCell.Offset(0, 0) = r(i).Name
ActiveCell.Offset(0, 5) = TSV
ActiveCell.Offset(0, 7) = tcost(TSV).Value

summ_row = summ_row + 1
Range("A" & summ_row).Select
End If
Next

End If
Next
 
Top