Beefy Boxes and Bandwidth Generously Provided by pair Networks
Perl Monk, Perl Meditation

Microsoft Project and Perl

by dws (Chancellor)
on Sep 05, 2001 at 10:46 UTC ( [id://110228]=perltutorial: 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.)

Replies are listed 'Best First'.
Re: Microsoft Project and Perl
by BigJoe (Curate) on Sep 05, 2001 at 16:54 UTC
    dws, We are setting up MS project in Oracle. Which then gives you full access to the data in Perl. We are getting ready to use Project Central along with Project 2000 to Manage projects that we do. I am also writing a work order form that will dump the information directly into a project for instant managment.

    My suggestion is if you want to use Project to collaborate use a database it makes it easier to use with Perl.


    Learn patience, you must.
    Young PerlMonk, craves Not these things.
    Use the source Luke.
Re: Microsoft Project and Perl
by cacharbe (Curate) on Sep 06, 2001 at 07:19 UTC
    Beautiful Work.

    I, as well, am helping my corp implement Project2000 and Project Central with a SQL2000 back-end. Also, webparts designed to work in our B2E (Forgive me) portal and the ProjectCentral Portal workspaces for different development and engineering teams. The pilot project is the Lan/Wan implementation teams roll out plan for Win2k Server, XP and ADS for 250 odd sites world wide. (they gave me servers and everything..*pant*pant*)

    These little nuggets will definitely be tools for the utility belt come implemnentation time.

    Thanks for the great info.


Re: Microsoft Project and Perl
by Ratazong (Monsignor) on Jun 06, 2016 at 08:50 UTC

    I stumbled over this old node today. It gave me the start on using MS project with perl :-) Thanks!

    However the following line led to an error:

    my $project = $app->{Projects}->Item(1);
    In case you are get the same, use the following instead:
    my $project = $app->Projects(1);

    Hope this info helps future readers. Rata

Re: Microsoft Project and Perl
by wufnik (Friar) on Oct 14, 2004 at 12:36 UTC
    dws, this deserves upvoting on the basis of your first paragraph alone. sell it to m$oft...


    -- in the world of the mules there are no rules --
Microsoft Project and Perl under Unix
by Anonymous Monk on Jun 02, 2003 at 13:59 UTC
    I have to develop an application to parse a Microsoft project file(.mpp) and write into too under unix. I know spreadsheet module which can parse a Excel file but I don't Know if it could parse a .mpp file. If you have any information, I'll be happy to know about it. thanks. M.Guérin
      I have to develop an application to parse a Microsoft project file(.mpp) and write into too under unix.

      As far as I know, an .mpp file is an OLE container. There's work in the Java world to provide classes to read OLE containers directly. I don't know about writing them, and I don't know if anyone in the Perl world is translating that work. In other words, you've just been handed a very nasty research project. Were I you, I would push back on this one very hard. Reading/writing a .mpp is best done on Win32, using the official COM API.

Re: Microsoft Project and Perl
by Anonymous Monk on Nov 21, 2005 at 17:53 UTC
    I tried to run the sample code on Windows XP Pro with IndigoPerl. The first complaint from Perl was "Can't locate Mail/Mailer in @INC ...", which is OK since I know I don't have that package. I commented out the mail code and tried again. Now Perl says, "Couldn't open project at line 5." (I named the source file I do have a Project 2003 file called SomeProject.mpp in the active directory, so I don't know why it couldn't be opened. Any idea where to start on debugging this problem?

      You may also have on your machine something called "OLE Browser" or "Type Library Browser" if you installed Win32-OLE-0.1403 or upgraded from that to a later version. You might find it in C:\Perl\html\OLE-Browser or C:\Perl\html\lib\site\Win32\OLE depending on your install.

      Something else I've found to be very handy indeed is West Wind Technology's GetConstants utility, which allows you to look at all those cryptic constants MS products use. (A short snippet of output is below.) Note that they do shuffle the links around occasionally, so it may be necessary to do some searching. :-)

      *** Constant Group: WdTableFieldSeparator #define wdSeparateByParagraphs 0 #define wdSeparateByTabs 1 #define wdSeparateByCommas 2 #define wdSeparateByDefaultListSeparator 3



      I do have a Project 2003 file called SomeProject.mpp in the active directory, so I don't know why it couldn't be opened.

      I don't have access to any version of Project at the moment, but do recall that Microsoft changed some details between Project 98 and Project 2000. They might well have done the same for 2003. ActiveState provides an OLE Browser (it's in the start menu somewhere). I'd look first to see if the name of the OLE Control for dealing with Project has changed, or if it expects different parameters.

Log In?

What's my password?
Create A New User
Domain Nodelet?
Node Status?
node history
Node Type: perltutorial [id://110228]
and the web crawler heard nothing...

How do I use this?Last hourOther CB clients
Other Users?
Others examining the Monastery: (4)
As of 2024-05-22 05:46 GMT
Find Nodes?
    Voting Booth?

    No recent polls found