Eng-Tips is the largest engineering community on the Internet

Intelligent Work Forums for Engineering Professionals

  • Congratulations waross on being selected by the Eng-Tips community for having the most helpful posts in the forums last week. Way to Go!

How to summarize resource utilization by month

Status
Not open for further replies.

zzqv9p

Computer
Mar 30, 2008
1
CA
I have a VB script and I am trying to determine which variables I can use to produce the following matrix

resource name X YYYY-MMM (and report effort + cost).

I can extract from task the following
duration
start and finish time
I can then extract the resources per task
with this I can extract the resource name, rate, peak units
the resource cost I get is for the entire project and not the task.
what I am unable to figure out is how to extract by day/month the resource utilization.
this is a subsection of the code, that I have been trying to see what information I could use to get the extract I need
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
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top