perltutorial dws 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 <code>.mpp</code> files can be accessed through a COM interface, in much the same way that one can use Win32::OLE to reach into an <code>Excel</code> spreadsheet. A <code>Project</code> object model has, among other things, a collection of <code>Resources,</code> and a collection of <code>Tasks.</code> Each <code>Task</code> also contains a collection of the <code>Resources</code> that are assigned to that <code>Task.</code> (Tasks and Resources are also associated via an <code>Assignment,</code> though <code>Assignment</code> is a kind of "second class" object, since links to the corresponding <code>Task</code> and <code>Resource</code> are not directly navigable. Read: don't bother going there.) A <code>Resource</code> has a <code>Name.</code> (Isn't it nice to know that you aren't merely a number?) A <code>Resource</code> can also have an <code>EMailAddress,</code> which can come in very handy when doing automated things to plans that involve sending email. A <code>Task</code> has a <code>Start</code> date/time, a <code>Finish</code> date/time, and a <code>Duration,</code> which is represented as the number of working seconds between the <code>Start</code> and the <code>Finish</code> (a <code>Task</code> can span one or more non-working days). A <code>Duration</code> of 0 means that the task represents a "Milestone". <code>PercentComplete</code> is just that; a task that is 100 <code>PercentComplete</code> is finished. Each <code>Task</code> has associated <code>Notes.</code> Each <code>Task</code> has a collection of <code>PredecessorTasks</code> and a collection of <code>SuccessorTasks,</code> 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 <code>Task</code> has a non-empty collection of <code>OutlineChildren,</code> the <code>Task</code> is a parent in an outline. The parent inherits the earliest <code>Start</code> date from its children, and the latest <code>Finish</code> date. About Collections Collections 1-based arrays which are implemented using COM's <code>SAFEARRAY,</code> 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., <code>Start</code> and <code>Finish</code>) are <code>VARIANT</code>s. 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 <code>VT_DATE VARIANT</code> 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 <code>Excel</code> or <code>Word</code> file, this step holds no surprises. To get a specific project plan, you need to reach into the <code>Projects</code> collection to grab the first one. <code> 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); </code> 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. <code> my $current_date = Variant(VT_DATE, $project->{CurrentDate}); </code> Now let us walk through the Tasks, looking for any that are overdue. <code> 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; } </code> 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. <code> my %overdue; foreach my $task ( @overdue ) { foreach my $ri ( $task->{Resources}->Count() ) { my $resource = $task->{Resources}->Item($ri); push @{$overdue{$resource->{EMailAddress}}}, $task; } } </code> The rest is easy. <code> 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(); } </code> 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.)