Macro to iterate through a field filter one at a time using VBA Microsoft project 2010

Question

Basically as the title suggest, I'm trying to iterate through a custom field, named "Text3" which is associated with "Task Owner", drop down filter one resource at a time. I am new at this so please bear with me.

Here is my code:

Sub Macro1()
' Macro Macro1
' Macro Recorded Fri 5/29/15 by Valencia, Jonathan.
' suppose to go down the task owner filtered list for each resource and
' print to xps

    Dim res As resource, name As String

    'apply the gantt view first
    ViewApply name:="gantt chart"
    'expand all tasks
    OutlineShowAllTasks
    'apply the late task filter
    FilterApply name:="Late Tasks"

For Each res In ActiveProject.Resources
    name = res.name


    'apply filter to task owner as the resource
    'checks to see if filter is set on the application first
    If Not ActiveProject.AutoFilter Then
        Application.AutoFilter
    End If

    Application.SetAutoFilter FieldName:="Text3", _
                FilterType:=pjAutoFilterCustom, _
                Test1:="contains", Criteria1:=name


    'export to xps with the resources' name
    DocumentExport FileName:=name, FileType:=pjXPS



Next res


End Sub
*************************************************************************

The problem I'm having is it's not setting up the filter for that certain resource and its just leaving it blank. It works if I give the criterial as e.g "john smith", but I'm trying to use the varible name as a string to iterate through all the resources.

Any help would be appreciated. Thanks.

Are you sure the values you have in Text3 match your resource names exactly? I tried your code and it worked fine for me. For example, I created a resource named "Rachel" and put that value in the Text3 field for some late tasks and the filter worked perfectly. — Rachel Hettinger
I just checked and you are right the names do not exactly match up... — Jonathan Valencia
How would I iterate through that filtered list then? Is there an array with all the values stored for that filtered list? — Jonathan Valencia

Rachel Hettinger Rachel Hettinger · Accepted Answer · 2015-06-04T16:53:04

Per your comments, this code will build a list of unique names stored in the Text3 field and then loop over them to create the XPS reports.

Sub CreateXpsReports()

    ' build unique list of names store in Text3
    On Error Resume Next
    Dim Names As New Collection
    Dim tsk As Task
    For Each tsk In ActiveProject.Tasks
        Names.Add tsk.Text3, tsk.Text3
    Next tsk

    On Error GoTo 0

    'apply the gantt view first
    ViewApply name:="gantt chart"
    'expand all tasks
    OutlineShowAllTasks
    'apply the late task filter
    FilterApply name:="Late Tasks"

    Dim name As Variant
    For Each name In Names
        'This was the only line I was missing for the code to work.
        On Error Resume Next           

        'apply filter to task owner as the resource
        'checks to see if filter is set on the application first
        If Not ActiveProject.AutoFilter Then
            Application.AutoFilter
        End If

        Application.SetAutoFilter FieldName:="Text3", _
                    FilterType:=pjAutoFilterCustom, _
                    Test1:="contains", Criteria1:=name

        'export to xps with the resources' name
        DocumentExport FileName:=name, FileType:=pjXPS

    Next name

End Sub

Macro to iterate through a field filter one at a time using VBA Microsoft project 2010

1 Answers