MS Project Navigating Resources and Assignments in RESOURCE USAGE view

Question

Regards. I am looking for a code in VBA that allows me, in the RESOURCE USAGE view, to go through the resources with assignment in the ResourceField with a FOR NEXT, and extract their assignment information from the TimescaleRange (right panel with tabular information), in order to be able to format the information and export it upon decision.

Capture of the RESOURCE USAGE view

I expand a little the information of my concern... in the capture I present the Resource Usage view, I take that information and with the list of resources on the left, I copy and paste it in excel; With this information I create my resource histogram, but I want to generate the export process through a VBA macro since there are several steps to perform (filtering, organizing the timeline of the view, and exporting the information of start to finish).

Answer 1

This code will loop through the resources in the active project and get work hours by resource by week.

Sub GetWorkByResourceByAssignment()

    Dim dteStart As Date
    Dim dteEnd As Date

    dteStart = #5/24/2021#
    dteEnd = #8/2/2021#
    
    Dim res As Resource
    Dim a As Assignment
    Dim tsvs As TimeScaleValues
    Dim tsv As TimeScaleValue
    
    For Each res In ActiveProject.Resources
        For Each a In res.Assignments
    
            Set tsvs = a.TimeScaleData(StartDate:=dteStart, _
                                       EndDate:=dteEnd, _
                                       Type:=pjAssignmentTimescaledWork, _
                                       TimeScaleUnit:=pjTimescaleWeeks)
            ReDim workHours(tsvs.Count) As Long
            Dim strValues As String
            strValues = vbNullString
            Dim i As Integer
            i = 0
            For Each tsv In tsvs
                i = i + 1
                workHours(i) = Val(tsv.Value) / 60
                strValues = strValues & ", " & workHours(i)
            Next tsv
            Debug.Print res.Name, a.Task.Name, strValues
        Next
    Next
    
End Sub

The variable strValues and the debug statement are for demonstration purposes. The array workHours contains the numeric representation of the hours which can be used for the histogram.