Shared resource pool and allocation/availability

[sriniluminaire]

I recently created a shared resource pool for about 20 projects and have been able to use VBA to pull TimePhased Data for resources across months. However, now I'm trying to find a way to see what resource load each connected/attached project has. My goal is to use the resource pool project, rather than go to each of the 20 individual projects. In MS Project, I can see the name of the project file, but can't find a way to export this information.

I debated between posting this as VBA question and a MS Project question and ended here.

Anybody have some ideas?

Thanks

 

[PDQBach]

I'm not sure what you mean by "exporting this information". If you can see it then you can copy and paste it or assign it to a variable.

 

[PDQBach]

sriniluminaire has logged in recently but didn't reply with an explanation of what he means by "exporting this information." Pending further explanation, I can't really help much (though I do have -- somewhere, have to hope I can find it again -- some VBA that starts to answer his question) and I will need some further clarification on his requirements.

 

[sriniluminaire]

I think the original question could use some rewording. However, I have been able to find what I want and thought I should share, in case others are interested.

Problem
There are ~20 projects and management wants to know what is the total effort across all of these projects, grouped by resources. They also want to know how much effort an individual resource will be assiged to by each project.

My solution
1. Total effort by resource
I was able to get Total effort by resource across all projects by using a method I found at

http://www.badsoda.com/tools/time-scaled-resource-data-exporter/.

It uses the ActiveProject.ProjectSummaryTask. TimeScaleData() method effectively.

2. Effort by resource in each project
When I went to extract this information, I wanted to inclue the Project column which I did not find this in the ProjectSummaryTask. With some research on TimeScaleData I found that it is available in a number of other places and that Resource.Assignments.TimeScaleData() will tell you how much effort each resource is assigned to a task in each project.
I iterated through each resource, found what tasks they were assigned to, for how many hours and when.

The beauty of what I discovered was that all of this can be done against the Shared Resource Pool and you don't need to have access to the individual project files.

 

[PDQBach]

Here's what I had put together while waiting for your reply (as always, beware of line wraps):

Sub PDQBach()
Dim asgn As Assignment
Dim res As Resource
Dim asgnTSV As TimeScaleValue
Dim asgnTSVs As TimeScaleValues


For Each res In ActiveProject.Resources
    Debug.Print res.Name; " "; res.Assignments.Count
    For Each asgn In res.Assignments
        Debug.Print "   "; asgn.Project; " "; asgn.Work
        Set asgnTSVs = asgn.TimeScaleData(StartDate:=asgn.Start, EndDate:=asgn.Finish, _
                                       Type:=pjAssignmentTimescaledWork, _
                                       timescaleunit:=pjTimescaleWeeks, Count:=1)
        For Each asgnTSV In asgnTSVs
            Debug.Print "        "; asgn.Project; "("; asgn.TaskName; ")"; " "; _
                                    asgnTSV.StartDate; " "; asgnTSV.EndDate; " "; _
                                    asgnTSV.Value / 480
        Next
    Next
Next