Trying to name a resource filter in MS Project

Question

Good morning,

I'm trying to set up a VBA automation to cycle through each resource name, and print out a single sheet overview of the entire project that uses that resource (so my guys have a personalized task list of what they need to be doing).

I can pull the value of the various resources assigned to the project, and control the view using:

ResourceName = ActiveProject.Resources(i).Name
ViewApplyEx Name:="&Gantt Chart", ApplyTo:=0

and I can access the filter using:

FilterApply Name:="Using Resource..."

where I get stuck is that I can't seem to actually set the resource value I want. I've tried setting Value1:= "SoAndSo", but that doesn't seem to work (the window popup where I'd set the value doesn't close).

I get a similar problem when I'm trying to print the window. I can get to the print window, but can't get the actual button to register so the printer gets the job.

I'm sure it's a small syntax thing, but I'd appreciate any assistance. Thanks!

Gordon, have you tried the Resource Usage view and then filtering by the Name field? — john-project, Dec 22 '22 at 20:04
Yes, the resource view is a wonderful tool. Currently I manually set the view to the individual, and then go to print the page. What I'd like, however, is a single macro that cycles through all the resources at one go (select resource, print, select next resource, print, etc). I can get to setting the Filter to "by resource" in VBA, but I don't seem able to set the name of the resource I want to use. — Gordon Smith, Dec 23 '22 at 21:08
Gordon, if you are looking for a task (assignment) list for each resource then I would set up an outer loop to loop through all resources. An inner loop would find all assignments for that resource and set a Flag field for each assignment and the resource. Follow that with a filter on the Flag field and a print. Clear the Flag field each time through the outer resource loop. — john-project, Dec 23 '22 at 22:48

score 0 · Answer 1 · answered Dec 23 '22 at 22:00

0

This macro loops through the resources, sets the filter and then prints. It's easy to create a new, temporary filter using FilterEdit and then delete it afterwards using OrganizerDeleteItem. Also, the FilePrint method needs at least one parameter otherwise, with no parameters, the dialog box is presented.

Sub PrintTasksForEachResource()

    Dim r As Resource
    For Each r In ActiveProject.Resources
        FilterEdit Name:="tempRes", TaskFilter:=True, Create:=True _
            , OverwriteExisting:=True, FieldName:="Resource Names" _
            , Test:="contains", Value:=r.Name _
            , ShowSummaryTasks:=True
        FilterApply "tempRes"
        FilePrint Color:=True
    Next r

    FilterClear
    OrganizerDeleteItem pjFilters, ActiveProject.Name, "tempres"
    
End Sub

answered Dec 23 '22 at 22:00

Rachel Hettinger

7,927
2
21
31

OK, so I'm following the logic there, but the question remains why do I need to create what appears to be a custom filter that effectively does the same thing as the built-in one? Is it purely a "Project isn't smart enough to let me define the resource name in VBA for the built-in function, and I have to do the workaround" thing? Happy to accept the odd dance to the solution, but curious about the limitation. Thanks for the advice! – Gordon Smith Dec 27 '22 at 15:21
@GordonSmith The "Using Resource..." filter is an interactive filter--it is designed to be a quick way for the user to filter on various resources without having to create multiple hard-coded filters. It's not a matter of Project not being smart enough, it's that the purpose of this filter does not match how you want to use it, thus creating a temporary run-time filter is a better solution. – Rachel Hettinger Dec 28 '22 at 18:30

score 0 · Accepted Answer · answered Dec 24 '22 at 01:47

Here's the implementation of my comment about an outer loop (resource) and inner loop (assignments) that effectively creates a resource To Do list. Include whatever Resource/Assignment fields you need. I suggest you pull the timescale data all the way to the right before running the macro so it doesn't appear in the printout. Remove the "preview" option in the print statement when you are ready to print.

Dim r As Resource
Dim a As Assignment
For Each r In ActiveProject.Resources
    r.Flag1 = True
    For Each a In r.Assignments
        a.Flag1 = True
    Next a
    FilterEdit Name:="ResAss", taskfilter:=False, create:=True, overwriteexisting:=True, FieldName:="Flag1", _
        test:="equals", Value:="yes", ShowInMenu:=False, showsummarytasks:=False
    FilterApply Name:="ResAss"
    r.Flag1 = False
    For Each a In r.Assignments
        a.Flag1 = False
    Next a
    FilePrint preview:=True
    FilterClear
Next r
        
End Sub

Trying to name a resource filter in MS Project

2 Answers2