|Just another Perl shrine|
Microsoft Project and Perlby dws (Chancellor)
|on Sep 05, 2001 at 10:46 UTC||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.
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.
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.
Now let us walk through the Tasks, looking for any that are overdue.
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.
The rest is easy.
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).
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.)