Beefy Boxes and Bandwidth Generously Provided by pair Networks
more useful options

comment on

( #3333=superdoc: print w/replies, xml ) Need Help??
Microsoft Project is a tool that many managers use behind closed doors to prepare massive, wall-sized works of fiction for the entertainment of corporate executives. Closely read, these fictional plans prove convincingly that neither gravity nor even the speed of light are obstacles for the corporation's mighty horde of otherwise unruly developers.

MS Project seems at first to be a closed, cumbersome, single-user tool. But lurking beneath that gray, stoic exterior there is a COM object model that is accessible to Perl via Win32::OLE. Unfortunately, information on driving Project from Perl is lacking, and working examples are nowhere to be found.

A Quick Tour of the Project Object Model

Microsoft Project .mpp files can be accessed through a COM interface, in much the same way that one can use Win32::OLE to reach into an Excel spreadsheet.

A Project object model has, among other things, a collection of Resources, and a collection of Tasks. Each Task also contains a collection of the Resources that are assigned to that Task. (Tasks and Resources are also associated via an Assignment, though Assignment is a kind of "second class" object, since links to the corresponding Task and Resource are not directly navigable. Read: don't bother going there.)

A Resource has a Name. (Isn't it nice to know that you aren't merely a number?) A Resource can also have an EMailAddress, which can come in very handy when doing automated things to plans that involve sending email.

A Task has a Start date/time, a Finish date/time, and a Duration, which is represented as the number of working seconds between the Start and the Finish (a Task can span one or more non-working days). A Duration of 0 means that the task represents a "Milestone". PercentComplete is just that; a task that is 100 PercentComplete is finished. Each Task has associated Notes.

Each Task has a collection of PredecessorTasks and a collection of SuccessorTasks, either or both of which can be empty. If you need to trace out dependency graphs, you'll use one of both of these collections.

Tasks can be structured hierarchically into an outline. If a Task has a non-empty collection of OutlineChildren, the Task is a parent in an outline. The parent inherits the earliest Start date from its children, and the latest Finish date.

About Collections

Collections 1-based arrays which are implemented using COM's SAFEARRAY, and are not to be confused with Perl arrays.

A Warning About Dates

Microsoft's documentation on the Project object model claims that the date fields (e.g., Start and Finish) are VARIANTs. As far as I've been able to tell, this isn't true. You get a string, and need to manually convert it into a VT_DATE VARIANT if you need to have one.

Dissecting a Project Plan with Perl

First, open the project plan and extract the Project object. If you've ever used Win32::OLE to open up an Excel or Word file, this step holds no surprises. To get a specific project plan, you need to reach into the Projects collection to grab the first one.

use Win32::OLE; use Win32::OLE::Variant; use strict; my $app = Win32::OLE->GetObject("SomeProject.mpp") or die "Couldn't open project"; my $project = $app->{Projects}->Item(1);
When Project opens a project plan, it sets the "current date" for the project. If you open a project during "off hours" or on the weekend, the current date will be next valid working date/time. Don't be surprised if this ends up being Monday morning at 8am even though your watch says Saturday night at 11pm.
my $current_date = Variant(VT_DATE, $project->{CurrentDate});
Now let us walk through the Tasks, looking for any that are overdue.
my @overdue; foreach my $i ( 1 .. $project->{Tasks}->Count() ) { my $task = $project->{Tasks}->Item($i); my $finish = Variant(VT_DATE, $task->{Finish}); push @overdue, $task if $finish < $current_date && $task->{PercentComplete} < 100; }
Overdue tasks? Oh my. What do do? Let's send an automated nastygram! For each resource associated with an overdue tasks, collect all of that resource's overdue tasks.
my %overdue; foreach my $task ( @overdue ) { foreach my $ri ( $task->{Resources}->Count() ) { my $resource = $task->{Resources}->Item($ri); push @{$overdue{$resource->{EMailAddress}}}, $task; } }
The rest is easy.
use Mail::Mailer; foreach my $email ( keys %overdue ) { my $mailer = new Mail::Mailer(); $mailer->open(From => "Nag-o-Matic", To => $email, Subject => "Overdue tasks"); print $mailer "You are associated with the following overdue tasks:\n\n"; foreach my $task ( @{$overdue{$email}} ) { print $mailer '"', $task->{Name}, '" was due on ', $task->{Finish}, "\n"; } print "\nPlease send a status update to your Manager.\n"; $mailer->close(); }
Next Steps

The Project object model is considerably richer. The ActiveState Win32::OLE::Browser web page can provide some detail, or more if you installed the Visual Basic stuff along with Project (thus installing help files for the OLE type libraries).

In Closing

MS Project need no longer be a source of dull, paper-based wall art. With a bit of creative Perl scripting, a project plan can become a work of collaborative performance art.

(Spot a typo? /msg me.)

In reply to Microsoft Project and Perl by dws

Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
to format your post; it's "PerlMonks-approved HTML":

  • Are you posting in the right place? Check out Where do I post X? to know for sure.
  • Posts may use any of the Perl Monks Approved HTML tags. Currently these include the following:
    <code> <a> <b> <big> <blockquote> <br /> <dd> <dl> <dt> <em> <font> <h1> <h2> <h3> <h4> <h5> <h6> <hr /> <i> <li> <nbsp> <ol> <p> <small> <strike> <strong> <sub> <sup> <table> <td> <th> <tr> <tt> <u> <ul>
  • Snippets of code should be wrapped in <code> tags not <pre> tags. In fact, <pre> tags should generally be avoided. If they must be used, extreme care should be taken to ensure that their contents do not have long lines (<70 chars), in order to prevent horizontal scrolling (and possible janitor intervention).
  • Want more info? How to link or How to display code and escape characters are good places to start.
Log In?

What's my password?
Create A New User
Domain Nodelet?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others avoiding work at the Monastery: (1)
As of 2023-03-25 13:58 GMT
Find Nodes?
    Voting Booth?
    Which type of climate do you prefer to live in?

    Results (63 votes). Check out past polls.