Introduction
Microsoft Project files contain much information that is needed for project reports, but
the Project application does not contain all data that is needed for projects reports, such as
an open item list, weekly summary, etc. Through .NET Interoperability, the data from the
Microsoft Project file can be exported to other formats and combined with other data to generated comprehensive
project reports. For this example I am concentrating on
demonstrating how to export the data from the Microsoft Project not how to use the data, thus I am
creating a simple Excel file that summarizes the project's activities for the current week, displays the
tasks that are late, and displays the tasks that will be starting.
For a more detailed discussion on project reporting, refer to the discussion at the end of this article.
Background
This article uses the .NET Interop functionality and the Excel XML format. Though these technologies are
used, this article does not go into detail explaining these technologies as there are many other articles
that do that. This article focuses on exporting the Microsoft Project data.
Using the code
The MainForm
class is the application and a good place to start a top down evaluation of the
code. The MainForm
class does not contain any functon other that being a GUI. All the functionality
is contained in the Project
class and the Task
class.
The Task
class contains the work breakdown structure id, the name, the start data, and the finish date
of the Microsoft Project task. The Microsoft Project task object contains tons more data, but for this example,
these pieces are all that we are interested in. The Task
class was created for two reasons: (1) I wanted
to extract the data from the Microsoft Project file as fast as possible so that I can close it, thus I needed
to copy the data into my own class, and (2) per proper OO design, I wanted the task to be able to format its own
data for the Excel XML. The Task
class is basically a data storage class--it does not manipulate
the data. The only item to note about the Task
class is that for Excel XML, the DateTime
must be formatted as YYYY-MM-DDThh.mm.ss.mmmm and this is handled through the StartDateExcelFormat
and
FinishDateExcelFormat
properties.
The use of the Task
class is very simple. The Project
class just calls the constructor
passing the Microsoft Project task object, uses the StartData
and FinishDate
properties
to determine if the task is current, late, or future, and uses the ToXml
method to generate the
Excel XML.
The Project
class is the work horse class--this class is the key to the application, but again
it was designed for easy use in that the MainForm
class, which only uses three Project
class methods: Load
, Evaluate
, and Save
. The
Project
class complies with good OO design by having the class manipulate its own data.
All the magic with the Microsoft Project data occurs in the Load
method of Project
:
MSProject.ApplicationClass app = null;
string retVal = "";
Initialize();
try {
app = new MSProject.ApplicationClass();
app.Visible = false;
if(app.FileOpen(fileName, true, Type.Missing,
Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing,
MSProject.PjPoolOpen.pjPoolReadOnly, Type.Missing,
Type.Missing, Type.Missing, Type.Missing)) {
foreach(MSProject.Project proj in app.Projects) {
foreach(MSProject.Task task in proj.Tasks) {
if(task.OutlineChildren.Count == 0) {
tasks.Add(new PrjXlsRpt.Task(task));
}
}
}
} else {
retVal = "The MS Project file " + fileName +
" could not be opened.";
}
} catch (Exception ex) {
retVal = "Could not process the MS Project file " +
fileName + "." + System.Environment.NewLine +
ex.Message + System.Environment.NewLine +
ex.StackTrace;
}
if(app != null) {
app.Quit(MSProject.PjSaveType.pjDoNotSave);
}
return retVal;
As the example above demonstrates the steps are: (1) Open the Microsoft Project Application, (2) Open the
Microsoft Project File, (3) for the Project go through all the tasks and do what ever you want to them, and
(4) close the application. The only two items left to discuss are: Adding the Microsoft Project COM
components and Creating the Excel file.
Adding Microsoft Project COM Components
The Microsoft Project data is accessed using Microsoft Project COM components. Wrappers to these COM components need to be created.
Microsoft Visual Studio 2003 provides an easy method for including non-managed COM components into your managed code.
Microsoft Project must be loaded on your development system for you to follow these instructions. Microsoft Project 2003 and
Visual Studio 2003 are used in this example.
- From the main menu select Project > Add Reference.
- Go to the "COM" tab. Scroll down and select "Microsoft Project 11.0 Object Library"
- Click Select and click OK
When you go to Solution Explorer, you should see Microsoft.Office.Core
, MSHTML
, MSProject
, System.XML
,
VBIDE
under the References folder. The next step is to add the using
statement to your code.
For the using
statement I use the assignment format because for library specific code I prefer to
use the namespace part of the class name so that I know where the class is located. By using the assignment
format of the using
statement I can make the code more readable and still indicate the location of the
class.
using MSProject = Microsoft.Office.Interop.MSProject;
This is all that is required to add the Interop functionality to your project.
Creating the Excel File
The Excel report file could be created using the COM Interop like the Microsoft Project part of this example,
but whenever possible I try not to have my programs rely on the software installed on a user's PC. I could also use the
Excel ODBC driver, but that means that I would have to have a properly formatted Excel file. To keep the implementation easy for me,
I used Excel XML to create the Excel file.
The easiest way of working with Excel XML is to create the file in Excel, save the file in XML, and extract the pieces
that you will use as strings in your program. If you do this frequently like I do, you will have a tool that will
convert the XML text into code. Having such a tool saves a lot of time, but if you do not have a tool, you
should be able to make one quick.
The Excel XML that is created in the application is done through text strings. I do not use the System.Xml
classes because that takes too long. These strings are located in the XmlPart1
, XmlPart2
,
XmlPart3
, and XmlPart4
methods located in the XML
region of the
Project
class.
One important part to note is that for the <Table>
element, remove the ss:ExpandedRowCount
parameter.
The row count will grow based upon the number of tasks and we really do not need to keep track of this. If this
parameter is removed, the XML code is still functional.
Project Reports Discussion
I am a project manager and for my customers I need to submit weekly project status reports. These reports
change from customer to customer but normally contain schedule information, open item information,
budgetary information, and a summary of what was accomplished during the week. This information is
scattered across multiple systems/files with Microsoft Project being used to manage the schedule. Our customers
do not want to spend too much time evaluating the status of the project. They want these project reports to be
concise such that they can determine the status of the project in about 5 minutes. Submitting just the
Microsoft Project file is unacceptable because depending upon the size of the project, it can take more than
5 minutes to just review the schedule. Also, these Project Reports need to be in a format that can be
distributed to people who do not have Microsoft Project. So for us, there is a need to export the data from
Microsoft Project and use it else where.
The example in this article is basic--extremely basic, but it is not supposed to be an application in itself
but rather a demonstration on how to export the data from Microsoft Project and use it within your application.
Microsoft Project does provide methods for exporting data, but the goal here is not to have the user
complete many steps to get the data, but have the software perform this repetitive task. In my company
we suck in data from Microsoft Project, an Open Item database (Access), and a time card system (SQL Server) into an Access
database using a one click action. This database contains forms for authoring summary data and contains a
report to pull all the data together. The report is printed to a PDF file and emailed to our customer. This
application is trivial, but it saves our project managers about 30 minutes per report per project--a good cost
savings to our company.
I concentrated on discussing the use of Microsoft Project data for project reports, but it can be used
anywhere else in your organization. We also use Microsoft Project data to feed our estimating tools. What
ever you use the Microsoft Project data for, I hope that this article was a valuable starting point.
Good luck with your projects.
Michael Carey is the Head of Development for an Automation Integrator in Philadelphia, PA. Michael specializes in Batch Automation, Process History, and Factory to Enterprise Integration.