Beefy Boxes and Bandwidth Generously Provided by pair Networks
Syntactic Confectionery Delight
 
PerlMonks  

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

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



  • Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!
  • Read Where should I post X? if you're not absolutely sure you're posting in the right place.
  • Please read these before you post! —
  • Posts may use any of the Perl Monks Approved HTML tags:
    a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr
  • Outside of code tags, you may need to use entities for some characters:
            For:     Use:
    & &amp;
    < &lt;
    > &gt;
    [ &#91;
    ] &#93;
  • Link using PerlMonks shortcuts! What shortcuts can I use for linking?
  • See Writeup Formatting Tips and other pages linked from there for more info.
  • Log In?
    Username:
    Password:

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

    How do I use this? | Other CB clients
    Other Users?
    Others browsing the Monastery: (7)
    As of 2014-09-15 10:25 GMT
    Sections?
    Information?
    Find Nodes?
    Leftovers?
      Voting Booth?

      My favorite cookbook is:










      Results (146 votes), past polls