VBA for printing Gantt by week

[Warren Rust]

(reposted from the MS Project General list)

I did a quick search and didn't see this already covered.

I track upcoming planned events in a MSProject file (2002-Standard) to allow
me to see them in the Gantt format (as opposed to the Excel spreadsheet they
come to me on).
These events are from one to many days duration and I find it convenient to
view them by week to check for conflicts. I can print them manually by
filtering by date range and setting the same print range in the Print dialog
box. However, I would really like a macro to do that for me as I print these
out for the upcoming year at the beginning of each month.

I've written the following vb script:

Sub PrintWeek()
' Macro PrintWeek
' Macro Recorded Tue 12/12/06 by Warren Rust.
For i = 1 To 52
FilterApply Name:="Date &Range..."
FilePrint
Next i
End Sub

which steps me through the process and allows me to use keyboard only. But,
I would really like to know if it's possible to set this thing up to step
itself through the process on its own starting (Sunday through Saturday) at
the project start date and ending 52 weeks later.

any help would be appreciated (even if it's just expert advice to stop
banging my head against my monitor).

thanks,
- wr

 

[John]

(reposted from the MS Project General list)

I did a quick search and didn't see this already covered.

I track upcoming planned events in a MSProject file (2002-Standard) to allow
me to see them in the Gantt format (as opposed to the Excel spreadsheet they
come to me on).
These events are from one to many days duration and I find it convenient to
view them by week to check for conflicts. I can print them manually by
filtering by date range and setting the same print range in the Print dialog
box. However, I would really like a macro to do that for me as I print these
out for the upcoming year at the beginning of each month.

I've written the following vb script:

Sub PrintWeek()
' Macro PrintWeek
' Macro Recorded Tue 12/12/06 by Warren Rust.
For i = 1 To 52
FilterApply Name:="Date &Range..."
FilePrint
Next i
End Sub

which steps me through the process and allows me to use keyboard only. But,
I would really like to know if it's possible to set this thing up to step
itself through the process on its own starting (Sunday through Saturday) at
the project start date and ending 52 weeks later.

any help would be appreciated (even if it's just expert advice to stop
banging my head against my monitor).

thanks,
- wr

Warren,
A fairly simple change to your existing code should help. Instead of
using the FilePrint Method without arguments, include the date range
arguments as follows:
FilePrint fromdate:=CycleSt, todate:=CycleEnd

Where CycleSt and CycleEnd are variables incremented to represent each
week of the year. Using this approach you will NOT need the FilterApply
Method.

Try it. If you need more help, let us know.

John
Project MVP

 

[Warren Rust]

John replied to my query:

Warren,
A fairly simple change to your existing code should help. Instead of
using the FilePrint Method without arguments, include the date range
arguments as follows:
FilePrint fromdate:=CycleSt, todate:=CycleEnd

Where CycleSt and CycleEnd are variables incremented to represent each
week of the year. Using this approach you will NOT need the FilterApply
Method.

Try it. If you need more help, let us know.

John
Project MVP

yes, a little more help, if you wouldn't mind (I'm a vba illiterate) ...
How do you create the fromdate/todate variables (can I get a dialog ask for
a start date?) and get them to increment?; and is there a way to increment
(in the script) the Filter by Date Range?
thanks for helping me out,
- wr

 

[John]

John replied to my query:


yes, a little more help, if you wouldn't mind (I'm a vba illiterate) ...
How do you create the fromdate/todate variables (can I get a dialog ask for
a start date?) and get them to increment?; and is there a way to increment
(in the script) the Filter by Date Range?
thanks for helping me out,
- wr

Warren,
OK, I was afraid of that, but hey, that's why we are here. Although this
can be done in VBA, I wonder if a simpler solution might be worth
checking. Go to View/Reports/Assignments. Select the "To Do List" and
hit "edit". On the definition tab set the period for "weeks" (that
should be the default) and select the view table that shows the fields
you need. Then in the "Details" tab make sure the "schedule" option is
checked. When it's all set up, take a look at the result and see if it
gives you something close enough to what you want. I realize it won't
give a Gantt display, but it's something already in place.

If the above just doesn't scratch your itch, give me a day or two and
I'll create a macro to do what you originally requested (i.e. print out
of a weekly task list in Gantt format). Let me know.

John
Project MVP

 

[Warren Rust]

John was kind to suggest:

Warren,
OK, I was afraid of that, but hey, that's why we are here. Although this
can be done in VBA, I wonder if a simpler solution might be worth
checking. Go to View/Reports/Assignments. Select the "To Do List" and
hit "edit". On the definition tab set the period for "weeks" (that
should be the default) and select the view table that shows the fields
you need. Then in the "Details" tab make sure the "schedule" option is
checked. When it's all set up, take a look at the result and see if it
gives you something close enough to what you want. I realize it won't
give a Gantt display, but it's something already in place.

If the above just doesn't scratch your itch, give me a day or two and
I'll create a macro to do what you originally requested (i.e. print out
of a weekly task list in Gantt format). Let me know.

John
Project MVP

John,

While I've played around looking at some of the reports before, but I've
never looked at the Assignments reports before (guess 'cause I figured
without resources nothing would show up). The closest to being helpful was
the Rollup Table filtered by All Tasks. But, the real power of the Gantt
chart for me is the ability to quickly scan for conflicts in the schedule and
that is much harder to do with a table. So, my itch isn't quite scratched,
but thanks for the suggestion.

Thanks for your offer to help,

- wr

 

[John]

John was kind to suggest:


John,

While I've played around looking at some of the reports before, but I've
never looked at the Assignments reports before (guess 'cause I figured
without resources nothing would show up). The closest to being helpful was
the Rollup Table filtered by All Tasks. But, the real power of the Gantt
chart for me is the ability to quickly scan for conflicts in the schedule and
that is much harder to do with a table. So, my itch isn't quite scratched,
but thanks for the suggestion.

Thanks for your offer to help,

- wr

Warren,
Well you are correct that if no resources are assigned, there will be no
assignment data.

After I posted my last reply I got to thinking (dangerous!). If you only
want to print out a Gantt display by week, how is that going to show you
much of anything? I guess if you visually compare one's months weekly
charts with a previous month's weekly charts, you might catch a
difference but that's a lot of visual work. There are much easier ways
to see variances. For example, you could use a spare baseline and set it
each month and then simply compare the current schedule dates with the
previous month's baseline. If there are differences, (and this can be
shown in a separate spare field), they will pop out much more readily
than a graphical display that might be hard pressed to show if a task a
slipped a few days either way.

So I guess if you are asking for further help, contact me direct at the
address below and tell me exactly what you want. Include a sample file
(zipped). I can probably help you but I will need to understand a whole
lot more.

John
Project MVP
jensenj6atatcomcastdotdotnet
(remove obvious redundancies)

 

[Warren Rust]

Warren,
Well you are correct that if no resources are assigned, there will be no
assignment data.

After I posted my last reply I got to thinking (dangerous!). If you only
want to print out a Gantt display by week, how is that going to show you
much of anything? I guess if you visually compare one's months weekly
charts with a previous month's weekly charts, you might catch a
difference but that's a lot of visual work. There are much easier ways
to see variances. For example, you could use a spare baseline and set it
each month and then simply compare the current schedule dates with the
previous month's baseline. If there are differences, (and this can be
shown in a separate spare field), they will pop out much more readily
than a graphical display that might be hard pressed to show if a task a
slipped a few days either way.

So I guess if you are asking for further help, contact me direct at the
address below and tell me exactly what you want. Include a sample file
(zipped). I can probably help you but I will need to understand a whole
lot more.

John
Project MVP
jensenj6atatcomcastdotdotnet
(remove obvious redundancies)

Thanks, look for an email from rustrwSYMBOLFORATnuDOTcom