Importing / Linking MS Project Dates into Excel - Recommendations or #REF! Error Resolution Help

To those who may follow this thread.

The reason Shooter71"s edit to the macro generates an error (type mismatch) is because he added Baseline Start and Baseline Finish to the fields being exported as data type "Date". If a baseline is not set, on any task, the value of those Baseline fields will be the default "NA", which is not a "Date" data type. The best solution is of course to set a baseline but an alternate approach is to declare the BStart() and BFinish() arrays as type "Variant".

For reference, the macro code posted above is from this Wiki article:

https://social.technet.microsoft.com/wiki/contents/articles/32126.ms-project-truncated-project-notes-when-viewingexporting.aspx

John

Shooter71,

With paste links you will know things went south (i.e. corrupt) when data gets messed up, links don't work, you get error messages, or any number of other undesirable behaviors.

The macro in the Wiki article I suggested is one way to transfer data from Project to Excel. In that case, the macro does indeed create a new Excel file to dump the Project data. However, it is just as easy to export selected Project data to a specific Excel file or vice versa (i.e. Excel file to a specific Project file). Options are virtually endless.

The difference between option 2 and 3 is function and reliability. Option 2 is rolling the dice, option 3 (VBA) is winning the game.

You mention that your goal is to compare an Excel summary file to a Project file. That sounds a little different than what you original post noted. What exactly is the "compare" part? Do you have an existing Excel summary report that you simply want to update with Project data when there is a change in the Project schedule?

John

Hi John,

I was able to get the macro working which is a good thing. However, as I think about what I am trying to do with the data, I am not sure the how I can avoid linking two files together with a paste link:

Option 1:

Copy paste linked text from the SharePoint MS Project File into my Excel file that I am trying to keep updated with the most current current task dates for a summary report. This imports the data at first paste, but as soon as the file is closed and re-opened, I get the !REF# that initiated this post. John mentioned he does not think you can do dynamic links between a SharePoint MS Project file and Excel. Based upon my results, I would have to agree due to the !REF# error.

Option 2: Save MS Project File from SharePoint onto my desktop. Link the excel file cells to the desktop MS Project file using the same paste linked text. This seems to work. When I updated the MS Project File, I save and overwrite the prior desktop project file version. Now when I open up the excel file, it updates to the new desktop version of the ms project file. However, per John's comment this is prone to corruption so not sure how to know when this occurs.

Option 3: Use the macro that John suggested. However, this will export an excel file with all the data.

Now, I am trying to compare my excel summary file to the ms project dates - that is the whole point of this effort. However, the only way I can do this is not link my excel summary file to the new ms project data excel file and import / compare the data. So, in the end I am still doing a paste link from two different files. In Option 2 I am importing from a MS Project File into Excel and in Option 3 I am importing Excel into another Excel File. So in the end, is there really any difference between option 2 and 3 in my case?

Shooter71,

Yes I have several macros that export various Project data to Excel. Here's one you can take a look at and you might want to try it to see how it works. It includes the fields you noted and some others.

https://social.technet.microsoft.com/wiki/contents/articles/32126.ms-project-truncated-project-notes-when-viewingexporting.aspx

I also have other macros that I can share although I often work one-on-one with users and develop an export macro specific to their needs. If you want to try that, let me know.

The advantage of VBA over other methods (e.g. copy/paste, Paste Links, import/export Wizard) is that VBA works directly with objects in Project's underlying database. The "raw" data can be manipulated and formatted right in the code, so the end result is a finished output/report.

John

Shooter71,

As I recall files on SharePoint do not support dynamic linking as is used for Paste Links or master/subproject structures. I should also point out that even when Paste Links are used between desktop applications, or even within a given application, the link path is prone to corruption.

And as you note, using the import wizard imports all fields as text which may not be the most desirable format. A manual conversion is not easy but a macro in Excel can automate the process.

However, in my experience the best way to get data between Project and Excel (or vice versa) is with VBA and I've used that approach many times for myself and other users. If you are interested in this approach, let me know.

Hope this helps.

John