vbams-project

MS project VBA : how can I get the work hours of an assignment on a day or between two dates?

I am trying to read/get the work hours required by an assignment on each day, or between two dates on vba.

To give a concrete view of what I am trying to do : I have created a task starting on 11-mar-24, which will require 16 hours of work, assigned to a resource called DD task created

I would like to read how many work hours this assignment consumes from DD worktime on a specific day or on a date range.

(<=> I want to read/get in vba the work hours in green on the resource usage sheet shown below) resource usage for task created

I have tried to extract that data using the TimeScaleData Method, but it seems you can only enter data, and not read data from it.

I just need now the correct method / command to extract that data.

I thank you in advance for any help you provide.

For Each tsk In ActiveProject.Tasks
    
    Dim asn As Assignment
    For Each asn In tsk.Assignments
        If asn.Resource.Name = "DD" Then
            
            'Dim tsv As TimeScaleValues
            'Set tsv = asn.TimeScaleData(StartDate:=#3/1/2024#, EndDate:=#3/7/2024# _
            '    , Type:=pjAssignmentTimescaledActualWork, TimeScaleUnit:=pjTimescaleDays)
            
            testasn = asn.TimeScaleData(StartDate:=#3/1/2024#, EndDate:=#3/7/2024#)
            
            'test1 = tsv(1).Value
            'test2 = tsv(2).Value
                            
        End If
    Next asn
Next tsk
Solution

  • Found it.

    The macro below will return in the variable TestFeedback a text with :

    Just put a breakpoint on it to check how it evolves during the loop.

    Some very useful info can be found here : time phased data ms project

    There's some info there about how to read such hours per resource.

    Note : The page on the link mentions that the uniqueID should be entered in XXX in the expression Set tsvs = ActiveProject.Tasks(XXXX).TimeScaleData([...])

    However, this was not working when I tested the macro. It worked only with the ID of the task.

    Sub TimePhasedDataTest()
Dim tsv As TimeScaleValue
Dim tsvs As TimeScaleValues

TestStart = #3/9/2024#
TestFinish = #3/19/2024#
Set t = ActiveCell.Task
   'Timephased for Task with ID 6
   Set tsvs = ActiveProject.Tasks(6).TimeScaleData( _
      StartDate:=TestStart, _
      EndDate:=TestFinish, _
      Type:=pjTaskTimescaledWork, _
      TimeScaleUnit:=pjTimescaleDays, Count:=1)
   For Each tsv In tsvs
        'Debug.Print "Start: " & Format(tsv.StartDate, _
            '"Long Date"), "Work: " & Val(tsv.Value) / 60 & "h"
        TestFeedback = tsv.Parent.Name & _
            " S: " & tsv.StartDate & " " & _
            "F: " & tsv.EndDate & " " & _
            "W: " & Val(tsv.Value) / 60 & "h"
   Next tsv

End Sub