How can I create a persistent data connection from MS Project 2013 to Excel 2013?

Question

I'm trying to get dates from an MS Project file to come across to a separate Excel file which I'm using to run conditional formulae on to handle the project status matrix.

The catch is that I need those dates to update whenever the project file is changes, so a simple export/import isn't ideal - I need a persistent connection.

I've tried the copy>paste-special>link>text approach. That works perfectly for the initial data dump into Excel, and any updates to the still-open project file all come through. However, as soon as I close and re-open either the excel or project file, the links seem to corrupt (I've not changed the file names or locations).

How can I set up a connection that is at least persistent & robust, if not automatically-updated?

Answer 1

To do what you're asking you'd probably need to use project server and link Excel to the tasks table of the SQL server via a read only odbc connection, but even then it wouldnt be real time as the database is only updated when the schedule is pubished (and it would cost money in licensing).

MPP files can only be opened by one application at a time, so I don't think youd ever be able to do real time updates.

A compromise option could be a MS Excel document with macros that opens the MPP file on open and pulls down the up to date data, then closes the MPP file (or leaves it locked until the Excel file is closed, meaning further changes cant be made). This is based on the assumption that the machine running the Excel file also has MS Project installed - otherwse you won't be able to access the Project file.

Alternatively, add a macro to the MS Project save event which outputs the data you want to an Excel file. No additional licensing required, just some VBA knowhow.

I hope that helps.