Filter Question Regarding Actual Work for a specific time range

S

Sinister

I found this code on a previous post:

Sub DateRngwActuals()
Dim t As Task
Dim a As Assignment
Dim st As Date, fin As Date
Dim ActW As TimeScaleValues
Dim i As Integer

ViewApply Name:="gantt chart"
st = InputBox("Enter start date of range", "Actuals for period")
fin = InputBox("Enter finish date of range", "Actuals for period")
For Each t In activeproject.Tasks
If Not t Is Nothing Then
t.Flag1 = False
Set ActW = t.TimeScaleData(StartDate:=st, EndDate:=fin, _
Type:=pjTaskTimescaledActualWork,
timescaleunit:=pjTimescaleWeeks)
For i = 1 To ActW.count
If ActW(i).Value <> "" Then
t.Flag1 = True
Exit For
End If
Next i
End If
Next t
FilterEdit Name:="Actuals", taskfilter:=True, create:=True,
overwriteexisting:=True, _
FieldName:="flag1", test:="equals", Value:="yes", ShowInMenu:=False,
showsummarytasks:=False
FilterEdit Name:="Actuals", taskfilter:=True, operation:="and", _
newfieldname:="summary", test:="equals", Value:="no"
FilterApply Name:="Actuals"

End Sub

It's perfect except I need to see it in the resource usage view and I'd like
the filter to be called Weekly Assignments
 
J

Jan De Messemaeker

Hi,

If you want to filter on resources that work during that period here's how:

Sub DateRngwActuals()
Dim t As Resource
Dim a As Assignment
Dim st As Date, fin As Date
Dim ActW As TimeScaleValues
Dim i As Integer

ViewApply Name:="gantt chart"
st = InputBox("Enter start date of range", "Actuals for period")
fin = InputBox("Enter finish date of range", "Actuals for period")
For Each t In activeproject.resources
If Not t Is Nothing Then
t.Flag1 = False
Set ActW = t.TimeScaleData(StartDate:=st, EndDate:=fin, _
Type:=pjresourceTimescaledActualWork, timescaleunit:=pjTimescaleWeeks)
For i = 1 To ActW.count
If ActW(i).Value <> "" Then
t.Flag1 = True
Exit For
End If
Next i
End If
Next t
FilterEdit Name:="Weekly Assignments", taskfilter:=False, create:=True,
overwriteexisting:=True, _
FieldName:="flag1", test:="equals", Value:="yes", ShowInMenu:=False
FilterApply Name:="Weekly Assignments"

End Sub

Greetings,

--
Jan De Messemaeker
Microsoft Project Most Valuable Professional
+32 495 300 620
For availability check:
http://users.online.be/prom-ade/Calendar.pdf
 
S

Sinister

I tried the code but to no avail. I worked on it a bit over the weekend and
here's what I came up with:



Sub DateRngWeeklyWorkAssignments()

Dim tTask As Task
Dim aAssignment As Assignment
Dim dStart As Date, dFinish As Date
Dim tsvWork As TimeScaleValues
Dim iCount As Integer

' Change the current view to Weekly Resource Assignments Timesheet
ViewApply Name:="Resource Usage"

' Ask the user for the date range they want to use
dStart = InputBox("Enter start date of range", "Work for period")
dFinish = InputBox("Enter finish date of range", "Work for period")

' Keep executing this code until there are no more tasks left
For Each tTask In ActiveProject.Tasks

If Not tTask Is Nothing Then
tTask.Flag1 = False
Set tsvWork = tTask.TimeScaleData(StartDate:=dStart,
EndDate:=dFinish, _
Type:=pjTaskTimescaledWork, timescaleunit:=pjTimescaleWeeks)

For iCount = 1 To tsvWork.Count

If tsvWork(iCount).Value <> "" Then
tTask.Flag1 = True
Exit For
End If

Next iCount

End If

Next tTask

' Create a new filter in the filter menu for our newly created filter
FilterEdit Name:="Weekly Assignments", taskfilter:=False, create:=True,
overwriteexisting:=True, _
FieldName:="flag1", test:="equals", Value:="yes", ShowInMenu:=False,
showsummarytasks:=False
FilterEdit Name:="Weekly Assignments", taskfilter:=False, operation:="and", _
newfieldname:="summary", test:="equals", Value:="no"

' Apply our new filter
FilterApply Name:="Weekly Assignments"

End Sub

Unfortunately, I keep getting an error at this line:

FilterEdit Name:="Weekly Assignments", taskfilter:=False, operation:="and", _
newfieldname:="summary", test:="equals", Value:="no"

saying that the filteredit method failed.
 
J

Jan De Messemaeker

Oh Dear!

There are no summary resources.
Since you seem to mix these ideas, let's put one thing straight.
Are you trying to filter tasks or resources?
First think about what you want to do.
Then we'll discuss code.

Hope this helps,

--
Jan De Messemaeker
Microsoft Project Most Valuable Professional
+32 495 300 620
For availability check:
http://users.online.be/prom-ade/Calendar.pdf
 
S

Sinister

Hmmm, well I want to filter tasks in the resource usage view. Only tasks that
have work hours assigned to them for a specific week. For example:

BEFORE THE FILTER in the Resource Usage view

RESOURCE NAME M T W T F
=========================
John Doe
Task 1 2 2 2 2 2
Task 2 0 0 0 0 0

Mary Doe
Task 3 8 8 8 8 3
Task 4 0 0 0 0 5
-----------------------------------------------------
AFTER THE FILTER in the Resource Usage view

RESOURCE NAME M T W T F
=========================
Task 1 2 2 2 2 2
Task 3 8 8 8 8 3
Task 4 0 0 0 0 5

Task 2 was filtered out because there were no work hours assigned to it for
that week.

Thanks
Patrick
 
J

Jan De Messemaeker

Hi,

Sorry I din't read beyond the first phrase.
There are no tasks in the resource usage view, only resources and
assignments.
This being said, I'll read any way and try to help you.

--
Jan De Messemaeker
Microsoft Project Most Valuable Professional
+32 495 300 620
For availability check:
http://users.online.be/prom-ade/Calendar.pdf
 
J

Jan De Messemaeker

Is there any reason you would do this in the resource usage view rather than
in the task usage view?
And if it's in the resource usage view, whatever happenend to Mary Doe?

--
Jan De Messemaeker
Microsoft Project Most Valuable Professional
+32 495 300 620
For availability check:
http://users.online.be/prom-ade/Calendar.pdf
 
J

Jan De Messemaeker

Even one more question.
Suppose John and Mary both work on Task7
John has work on task7 during the week, Mary Hasn't
Do you want to show task7 below Mary or not?

--
Jan De Messemaeker
Microsoft Project Most Valuable Professional
+32 495 300 620
For availability check:
http://users.online.be/prom-ade/Calendar.pdf
 
S

Sinister

The goal is to have a split view with the resource assignments on the top and
the gantt chart on the bottom. I like this arrangement because I can select a
task assignment under a resource and just the selected tasks appear in the
gantt chart in the bottom view. This gives me the option of using the split
tool to reallocate tasks on the bottom in the gantt chart view and/or
changing the work hours in the top resource view. It's a great way to review
what people have assignemed in the coming weeks and changing workload on the
fly in a very flexible view. I can do this now but I have to scroll through a
lot of assignments in the resource usage view before I come across ones that
have work assigned for that particular week. A filter to weed out the ones
that don't have work for the spedified week would be a great help.

Thanks for sticking with me on this.
Patrick
 
J

Jan De Messemaeker

Hi,

OK, here we go


Sub F1Filt()
Dim Boy As Resource
Dim Job As Assignment
Dim dStart As Date
Dim dFinish As Date
Dim tsvWork As TimeScaleValues
Dim iCount As Integer

' Change the current view to Weekly Resource Assignments Timesheet
ViewApply "Resource Usage"
' Ask the user for the date range they want to use
dStart = InputBox("Enter start date of range", "Work for period")
dFinish = InputBox("Enter finish date of range", "Work for period")

For Each Boy In ActiveProject.Resources
For Each Job In Boy.Assignments
Job.Flag1 = False
Set tsvWork = Job.TimeScaleData(StartDate:=dStart, EndDate:=dFinish, _
Type:=pjAssignmentTimescaledWork, timescaleunit:=pjTimescaleWeeks)
For iCount = 1 To tsvWork.Count
If Val(tsvWork(iCount).Value) > 0 Then
Job.Flag1 = True
Exit For
End If
Next iCount
Next Job
Next Boy

' Create a new filter in the filter menu for our newly created filter
On Error Resume Next
OrganizerDeleteItem Type:=pjFilters, FileName:=ActiveProject.FullName,
Name:="Weekly Assignments", Task:=False
On Error GoTo 0
FilterEdit Name:="Weekly Assignments", taskfilter:=False, Create:=True, _
FieldName:="flag1", test:="equals", Value:="yes", ShowInMenu:=False, _
showsummarytasks:=True
' Apply our new filter
FilterApply Name:="Weekly Assignments"
End Sub

Hope this helps,


--
Jan De Messemaeker
Microsoft Project Most Valuable Professional
+32 495 300 620
For availability check:
http://users.online.be/prom-ade/Calendar.pdf
 
S

Sinister

Thank you so much. How do I go about rating you. I want to make sure you get
props for all this work.
 
J

Jan De Messemaeker

Hi,

That's lovely, thank you.
I can only hope people in Belgium or Holland read this and think hmmm, this
man can certainly help us..
Thanks again for the kind words!

--
Jan De Messemaeker
Microsoft Project Most Valuable Professional
+32 495 300 620
For availability check:
http://users.online.be/prom-ade/Calendar.pdf
 
J

Jrufin

Hi Jan,
Can you please help me on this:
I need a VBA code to export TimeScaleData to Excel.
Scenario:
I have 20 resources and I want to export to Excel their Work Assigment for
the following specific time period (in fiscal calendar):
Period 1: Jan 1 to Jan 18
Period 2: Jan 19 to Feb 15
Period 3: Feb 16 to Mar 21
Period 4: Mar 22 to Apr 18
..
..
..
Period 12: Nov 15 to Dec 31


Thanks
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

multiple criterias for FilterEdit method, VBA? 2
Save contents of the schedule as a GIF 3
Filter on Actual Work for a specific time range 6
FilterEdit and Project 2003 not liking test criteria 11
VBA Macro - Copying specific data to different sheets in a workbookand linking the InputBox to a com 7
Extract Task Filter Properties 8
Print Custom Report w/ certain Resources 1
Project 2003 macro filters 1

Top