Problems with a filter in VBA

R

ronmcfarlane

I have defined a filter "Tasks due by..." as

Fieldname Test Values
%Complete does not equal 100% and
Summary equals No and
Start <= "Enter the report end date:"? and
Resource Name equals "Enter the Resource:"?


When I apply the filter through the list of Project>Filter for>Task due
by... everything works fine.

If however I try and use it in VBA It returns No tasks. The code is as
follows


Sub Tracking_Report()

Dim R As Resource
Dim Report_End As String

ViewApply Name:="Detail Gantt"
TableApply Name:="Task Tracking"
Report_End = InputBox$("Enter the Report End Date:")


'Loop for each Resource

For Each R In ActiveProject.Resources
FilterApply Name:="Tasks due by...", Highlight:=False,
Value1:=Report_End, Value2:=R.Name
....... logic to print a report

Next R
End Sub

The values for Report_End and R.Name look correct. If I remove the name
part and only filter on dates it works fine.

Also after applying the filter how can I determine a count of tasks so
that I can not print an empty report.

Thanks in advance

Ron
 
R

Rod Gill

Hi,

If you normally use d/m/yy date format, try m/d/yy format.

Failing that, record a macro of you applying teh filter and see what it
records..
 
J

John

I have defined a filter "Tasks due by..." as

Fieldname Test Values
%Complete does not equal 100% and
Summary equals No and
Start <= "Enter the report end date:"? and
Resource Name equals "Enter the Resource:"?


When I apply the filter through the list of Project>Filter for>Task due
by... everything works fine.

If however I try and use it in VBA It returns No tasks. The code is as
follows


Sub Tracking_Report()

Dim R As Resource
Dim Report_End As String

ViewApply Name:="Detail Gantt"
TableApply Name:="Task Tracking"
Report_End = InputBox$("Enter the Report End Date:")


'Loop for each Resource

For Each R In ActiveProject.Resources
FilterApply Name:="Tasks due by...", Highlight:=False,
Value1:=Report_End, Value2:=R.Name
....... logic to print a report

Next R
End Sub

The values for Report_End and R.Name look correct. If I remove the name
part and only filter on dates it works fine.

Also after applying the filter how can I determine a count of tasks so
that I can not print an empty report.

Thanks in advance

Ron

Ron,
If you want to use VBA why not do everything in VBA (i.e. create the
filter, automatically set the two week window and print each resource's
report)? Without actually testing your code out, it seems to me there is
a basic flaw in the implementation. The code loops through each
resource. As it loops it applies a filter but that filter appears to be
a task filter when in fact it should probably be a resource filter (at
least that might explain why the "name" portion doesn't work - in a task
filter resources are identified by the "Resource Names" field while in a
resource filter resources are identified by the "Name" field).

Instead of looping through resources why not loop through tasks. Set the
filter (using the FilterEdit Method) to look for all tasks that have a
Start date less than or equal to a date two weeks from the current date
or a Finish date less than or equal to a date two weeks from the current
date and a Resource Name that is modified automatically each time
through the print loop. It might even be beneficial to sort the whole
file by Resource Names (do not keep outline structure) before applying
the filter. I suggest looping through tasks instead of resources because
filtering on resource dates may give tasks (assignments) outside the
range of interest (remember, resource start and finish dates span the
resources entire assignment period).

As far as not printing blank pages when no assignments appear for a
particular resource in the time span, a selected set construct can be
used, for example:
On Error Resume Next
SelectTaskColumn
Set Area = ActiveSelection.Tasks
If Err > 0 then
[skip printing]
On Error GoTo 0 'clears error
Else
[print]
End if

Hope this helps.
John
Project MVP
 
R

ronmcfarlane

Thanks John for your reply. Please forgive my ignorance to this. I am a
COBOL programmer at heart so please understand this VBA ia all a bit
foreign.


What your telling me is that even though the detailed Gantt View is of
a task table and the filter is a task filter I cannot loop through
resources applying the filter each time on resource? The filter is on
Resource name rather than Name (task).

If I change this to loop on tasks then how do I change the Resource
name each time through the loop. Surely that implies I am looping on
resources.

Thanks

Ron
 
J

John

Ron,
Ok, the light bulb finally went on. It"s been a long time since I used
the FilterApply Method with two values (I almost always set up my
filters in the VBA code itself). Here is the reason you get the error
message. Your filter has two separately entered interactive values but
that is NOT what is represented by the two values in the FilterApply
Method. The two values are for a range of values on a single filter
entry line. For example, suppose the user wants to use the "is within"
test criteria and he wants to use it for a range of task start dates in
an interactive filter. Using the FilterApply Method in VBA value 1 would
be the first date and value 2 would be for second date. Does this make
sense? I hope so because that's how it works and unfortunately the VBA
help file for the FilterApply Method is essentially worthless.

So how could you change your simple macro to get what you want? Try this
loop and filter code:

For Each r In ActiveProject.Resources
FilterEdit Name:="TskDueBy", taskfilter:=True, Create:=True, _
OverwriteExisting:=True, FieldName:="% Complete", test:="does
not equal", _
Value:="100%", showinmenu:=False, showsummarytasks:=False
FilterEdit Name:="TskDueBy", taskfilter:=True, _
operation:="and", newfieldname:="start", test:="is less than or
equal to", _
Value:=Report_End
FilterEdit Name:="TskDueBy", taskfilter:=True, operation:="and", _
newfieldname:="resource names", test:="contains", Value:=r.Name
FilterApply Name:="TskDueBy"
Next r

Hope this helps.
John
Project MVP
 
R

ronmcfarlane

Finally something that makes sense. I am on leave now but I am looking
forward to trying this on my return.

Thanks

Ron
 
J

John

Finally something that makes sense. I am on leave now but I am looking
forward to trying this on my return.

Thanks

Ron
roject MVP

Ron,
Yeah, I kinda got off on a tangent with my original response. The second
round I had more time to do the necessary research. Let us know how it
works for you.

John
Project MVP
 
R

ronmcfarlane

Thanks John it works perfectly. There is just one slight improvement I
can make. Each time the FilePrint is executed the printer dialog box
appears and I have to click okay. It is only a minor thing but I may
have to do this 20 times when I run the macro.

Thanks again.
 
J

John

Thanks John it works perfectly. There is just one slight improvement I
can make. Each time the FilePrint is executed the printer dialog box
appears and I have to click okay. It is only a minor thing but I may
have to do this 20 times when I run the macro.

Thanks again.


Ron,
Ah but there's a trick to that. Several of the Methods in VBA will
default to a user window if certain arguments are not supplied. The VBA
help file states that for most of those methods but not for the
FilePrint Method. If you specify at least one argument (e.g. FilePrint
FromPage:= 1) you won't have to deal withe the pop up window.

Hope this helps.
John
Project MVP
 

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

Top