×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

How to summarize resource utilization by month

How to summarize resource utilization by month

How to summarize resource utilization by month

(OP)
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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members!


Resources