Rule of Thumb

Microsoft Project is very clumsy in providing usable reports. Some basic reports are there, but not very helpful and not really customizable.

Exporting (saving) Project data to Microsoft Excel is as helpful as reports. Start with duration and work fields, which are exported as text, with different time units (hours, days, months, …), and not as numbers, so you can’t do any analysis on them, without first converting them to numbers, probably with Data, then Text to Columns, and even then, you’d have to write one hell of a formula to convert those numbers to the same time unit.

Project provides quite extensive tool to define fields you want to export, headings and column sequence, but the end result is useless:

You’d think that exporting to Microsoft Excel PivotTable might do the trick, but no, work and duration are still textual, so PivotTable can only count them and cannot possibly sum. Why on earth would anyone need that?

But it does a decent job in replicating the outline structure, if that’s of some use to you.

Copy/pasting is as useless as exporting the data, so what can you do?

The solution is to dig really deep into the root of the problem. The data. Microsoft Project can work with ODBC databases instead of .mpp files, which is really clever. Forget about Microsoft Project reporting and exporting capabilities and use Microsoft Excel or Microsoft Access for this by directly attaching to the Microsoft Project database.

You don’t need a Microsoft Project Server to do this and neither a database server. The idea is to create an Microsoft Access database by saving the project in Microsoft Access Database (*.mdb) format.

The result is, that all the data, including all the options and settings, are in the Microsoft Access Database file, from which it is relatively easy to get it into Microsoft Excel, either as a worksheet table or as a PivotTable.

1. Start Microsoft Project with an empty project or by opening an existing one.
2. Select File, Save As and choose Microsoft Access Database (*.mdb) file format (Save as type drop down).
3. Browse to your preferred location and type the filename.
4. Confirm your choice by clicking Save.

1. Click Next.

1. Choose between a full or partial project export and click Next.

1. Microsoft Access Database can hold many projects. In the Export Wizard - Project Definition you need to define the name of the current project under Project name. This is not a filename, which is already set and you can see it next to Data Source.
2. Click Finish when you’re done.

To work with Microsoft Access Database file in Microsoft Project, you don’t double click it, as this will open Microsoft Access and not Microsoft Project. Instead, you run the Microsoft Project first and then you select File and Open.

Now you have two options:

• Select Microsoft Access Database (*.mdb) in Files of type and browse to your file,
• or click ODBC and choose the appropriate data source from the list. For this to work, you need to create a data source first. I suggest this approach, as you’ll need the data source for queries anyhow.

Now you have everything set up to create database query in Microsoft Excel. I’ll describe how to decipher project data in Access Database in upcoming posts.