Modifying reports code using VBA

[Hall]

I've mustered up the ambition to use VBA to create a custom report we need.

Is it possible to get the code for an existing canned report within MS
Project and modify it to get the custom results I want?

 

[Gérard Ducouret]

Hello Hall,

Most of the custom reports can be built via an interactive way without any
VBA programming.
You can use some standard views, copy them and modify the copy as you need.

Gérard Ducouret

 

[Hall]

Gérard

Here is the kind of report I want. I have not been able to see how using
the custom reports tool allows this.

All the data I need for this report is in the "entry" table. The fields
needed are: resource, start, finish, work, task, summary task, id,
predecessor, successor, slack.

Each page must have all the tasks for the current WEEK, per resource. I
want a column for each day of the week (in that same page) and to show the
work (hours) per task. Something like this:

ID
Start Finish Pred Succ Slack 1/5 1/6 1/7 1/8
1/9
Paul
Setup physical environment 14 12/22 1/18
10 8 8 10 4
Ducts 15
12/28 1/7 10 16 0 8 8 8
Wiring 16 1/1
1/7 15 22 0 2 6
4
HVAC 22
Annie
......

Output must be limited to the current week or specified week. I find the
reports' filter feature doesn't always work well for dates. Reports seem to
continue to the right on additional pages (!).

This report can give all the team members a clear picture of what they need
to do each week and what the impact is. The slack field also tells them if
the task is on the critical path.

So do you think I need VBA for this? If yes, I'd hate to do all this from
scratch. I'd much prefer to start with an existing similar report's code
and modify it to my report.

 

[John]

Hall,
Looking at your sample report form, as far as I can tell you will need
to use VBA to develop the custom report you desire. Although the
built-in report feature does allow some customization, it does have some
limitations. However, you can get what you need by using the Resource
Usage View except you will have to employ a trick to show the
Predecessors, Successors and Total Slack since these are task fields and
not available in a Resource view. The "trick" is to copy these fields
from the task view to spare resource fields such as the Resource text
fields. I admit this method is a bit mickey mouse but it does avoid
using VBA. By the way, I assume when you talk about "slack" you are
referring to Total Slack and not Free Slack. Only Total Slack will give
you the correct status with resprect to critical path.

If you don't like the non-VBA approach suggested above and have your
heart set on a true custom report, did you download the VBA primer from
the MVP website that I suggested in an earlier response?

John

 

[Hall]

Hi John

Thanks for the additional advice.

I looked at Resource Usage view and, like some of the reports, is close but
really needs some better filtering and formatting. It doesn't have the
actual task IDs (resources are numbered instead?!) so I'm not sure what good
I'd get from pred/successor columns (I wasn't clear on your trick by the
way...). And even though I chose a Date Range filter and then specified a
given week, the view and/or print preview still showed columns for all
weeks. I got that in the reports' Date Range filter - seems totally useless.

This is part of my frustration (and of my company's entire PMO on a large
SAP project among others) with this tool's reporting ability.

So then there's writing a report from scratch in VBA. OK, so I'm willing to
learn VBA for this though I'd be great to have access to the VBA code of one
of the existing reports. But I'm learning from these discussions that the
default reports are not available in VBA.

Yes, I looked at the primers you referred me to. They seem to be very
general on VBA and I think figuring out how to write a report with it will
take some time. We may have not choice but to use another tool at this
point.

Any thoughts from you to help save this?

 

[John]

Hall,
Since you have a specific need it may be easier to help you by working
with you directly. If interested, contact me via e-amil.

John

 

[Hall]

Thanks for the generous offer John.

At this point, we're not ready to contract someone to write the report for
us. That may come down the road, especially if we get more into the tool.

For now, the team members are ready to work with the columns as I'm
organizing to them. Its more manual than I'd like and a lot more info than
needed but we've accepted the limitations in the product.