Filtering Timephased Resource Usage Data by WBS Code

[BruceB]

I would like to write a VBA routine to export Resource usage data, e.g. work,
by phased time (months) to an Excel workbook but with a filter that extracts
only that work done within a specified range of WBS codes. For example, only
that time worked in tasks having a WBS code of 5.x would be exported for each
resource. I have done simple resource usage exports thanks to Rod Gill's
excellent examples in "VBA Programming for Microsoft Office Project", but I
can't see how to add the above filter in a timephased data approach.

Any suggestions would be very welcome.

 

[John]

I would like to write a VBA routine to export Resource usage data, e.g. work,
by phased time (months) to an Excel workbook but with a filter that extracts
only that work done within a specified range of WBS codes. For example, only
that time worked in tasks having a WBS code of 5.x would be exported for each
resource. I have done simple resource usage exports thanks to Rod Gill's
excellent examples in "VBA Programming for Microsoft Office Project", but I
can't see how to add the above filter in a timephased data approach.

Any suggestions would be very welcome.

Bruce,
I would first create a filter in VBA (FIlterEdit Method) to select out
the specific WBS for export. Then use the following code sequence to
loop through those tasks

[apply filter for WBS tasks]
SelectTaskColumn
For Each t in ActiveSelection.Tasks
[use your timesscaled data method here]
Next t

Hope this helps.
John
Project MVP

 

[BruceB]

John- Thanks, this is very helpful.
--
Bruce

I would like to write a VBA routine to export Resource usage data, e.g. work,
by phased time (months) to an Excel workbook but with a filter that extracts
only that work done within a specified range of WBS codes. For example, only
that time worked in tasks having a WBS code of 5.x would be exported for each
resource. I have done simple resource usage exports thanks to Rod Gill's
excellent examples in "VBA Programming for Microsoft Office Project", but I
can't see how to add the above filter in a timephased data approach.

Any suggestions would be very welcome.

Bruce,
I would first create a filter in VBA (FIlterEdit Method) to select out
the specific WBS for export. Then use the following code sequence to
loop through those tasks

[apply filter for WBS tasks]
SelectTaskColumn
For Each t in ActiveSelection.Tasks
[use your timesscaled data method here]
Next t

Hope this helps.
John
Project MVP

 

[John]

John- Thanks, this is very helpful.

Bruce,
You're welcome and thanks for the feedback.
John

I would like to write a VBA routine to export Resource usage data, e.g.
work,
by phased time (months) to an Excel workbook but with a filter that
extracts
only that work done within a specified range of WBS codes. For example,
only
that time worked in tasks having a WBS code of 5.x would be exported for
each
resource. I have done simple resource usage exports thanks to Rod Gill's
excellent examples in "VBA Programming for Microsoft Office Project", but
I
can't see how to add the above filter in a timephased data approach.

Any suggestions would be very welcome.

Bruce,
I would first create a filter in VBA (FIlterEdit Method) to select out
the specific WBS for export. Then use the following code sequence to
loop through those tasks

[apply filter for WBS tasks]
SelectTaskColumn
For Each t in ActiveSelection.Tasks
[use your timesscaled data method here]
Next t

Hope this helps.
John
Project MVP

 

[BruceB]

John- Just a follow up: I don't know whether VBA is buggy here or I am
missing something. When I use your code suggestion with a Task Usage Table
(Project 2003 SP2), I get an error every time the For Each t ... statement is
executed when the Task Usage Table is empty. For example:

SelectTaskColumn
For Each T In ActiveSelection.Tasks
MsgBox Val(T.Work) / 60
Next T

I can even do a test before executing the For loop such as:

If ActiveSelection.Tasks.Count > 0 Then
For Each T In ActiveSelection.Tasks
MsgBox Val(T.Work) / 60
Next T
End If

and now it throws an error on the If statement.

Any ideas?
--
Bruce

John- Thanks, this is very helpful.

Bruce,
You're welcome and thanks for the feedback.
John

I would like to write a VBA routine to export Resource usage data, e.g.
work,
by phased time (months) to an Excel workbook but with a filter that
extracts
only that work done within a specified range of WBS codes. For example,
only
that time worked in tasks having a WBS code of 5.x would be exported for
each
resource. I have done simple resource usage exports thanks to Rod Gill's
excellent examples in "VBA Programming for Microsoft Office Project", but
I
can't see how to add the above filter in a timephased data approach.

Any suggestions would be very welcome.

Bruce,
I would first create a filter in VBA (FIlterEdit Method) to select out
the specific WBS for export. Then use the following code sequence to
loop through those tasks

[apply filter for WBS tasks]
SelectTaskColumn
For Each t in ActiveSelection.Tasks
[use your timesscaled data method here]
Next t

Hope this helps.
John
Project MVP

 

[John]

John- Just a follow up: I don't know whether VBA is buggy here or I am
missing something. When I use your code suggestion with a Task Usage Table
(Project 2003 SP2), I get an error every time the For Each t ... statement is
executed when the Task Usage Table is empty. For example:

SelectTaskColumn
For Each T In ActiveSelection.Tasks
MsgBox Val(T.Work) / 60
Next T

I can even do a test before executing the For loop such as:

If ActiveSelection.Tasks.Count > 0 Then
For Each T In ActiveSelection.Tasks
MsgBox Val(T.Work) / 60
Next T
End If

and now it throws an error on the If statement.

Any ideas?

Bruce,
Yes, I should have included code that detects a "no data" condition as a
result of applying the filter. Try this,

SelectTaskColumn
On Error Resume Next
For Each t In ActiveSelection.Tasks
If Err = 0 Then
[your timescaled data method here]
Else
Exit For
End If
Next t
On Error GoTo 0

Note: the last line resets the Err Object if there are more macro code
steps that follow. If this loop effectively end the macro, resetting
isn't necessary since the Err Object is automatically reset when the
macro finishes.

John
Project MVP

John- Thanks, this is very helpful.

Bruce,
You're welcome and thanks for the feedback.
John

:

I would like to write a VBA routine to export Resource usage data,
e.g.
work,
by phased time (months) to an Excel workbook but with a filter that
extracts
only that work done within a specified range of WBS codes. For
example,
only
that time worked in tasks having a WBS code of 5.x would be exported
for
each
resource. I have done simple resource usage exports thanks to Rod
Gill's
excellent examples in "VBA Programming for Microsoft Office Project",
but
I
can't see how to add the above filter in a timephased data approach.

Any suggestions would be very welcome.

Bruce,
I would first create a filter in VBA (FIlterEdit Method) to select out
the specific WBS for export. Then use the following code sequence to
loop through those tasks

[apply filter for WBS tasks]
SelectTaskColumn
For Each t in ActiveSelection.Tasks
[use your timesscaled data method here]
Next t

Hope this helps.
John
Project MVP