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

Question

Software: MS Project Online Desktop Client MSO 16.0.13127.21210 32-bit use

I am trying to import MS Project start / finish dates from a MS project file that is on SharePoint into an excel file and keep the import linked between excel and MS project such that anytime the date changes in MS Project, my excel file dates update accordingly.

The first method I tried was selected and copying a date in the SharePoint MS Project File and doing a Paste Special - Paste Link - "As" text

This correctly inserted a date into my excel file using the following syntax:

=MSProject.Project.9|'<>\EUMDR PH-GT Therapy'!'!LINK_26'

The problem is as soon as I close the excel file and try to open it up again, all the dates change to #REF!. It does not seem like excel can keep the connection between the Excel Office 365 file and the SharePoint MS Project file. Is there any recommendations or settings on who to fix this. I completed a test in which I saved the MS Project File to my desktop and made the same Paste line command. This time the syntax looked like the following:

=MSProject.Project.9|'C:\Users\XXXXX\OneDrive - Company Name\Project Name\Product List\Timeline Summary\Archive\Timeline Summary 06-10-21.mpp'!'!LINK_23'

This time when I closed the excel file and re-opened it, it was able to re-establish the dates so it appears the paste link function is having an issue of maintaining a connection between excel and the SharePoint MS project file version. Any suggestions to fix the connection so I would not have to do yet another step to save an off-line version each time I want to insure no dates in my excel file as changed.

Alternatively, I have tried saving the MS Project file as a workbook and using the import wizard and map to export the data to excel. I figured if I could get that to work I could just have my excel file linked to the exported MS project workbook file. The problem with that is the dates are exported as text with a MM/DD/YYYY and TIME and once in excel there is not an easy way I have found to convert that test string into a date that excel recognized correctly.

Answer 1

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

Answer 2

Hello John,

I believe I have seen your similar reply on other like topics as I have search for prior solutions. I have not used VBA so this would be new territory for me but watching one 5 minute video, I at least know what VBA can do potentially, but seems it will come down to the ability to program a macro to extract data from MS project into Excel which may be a little complex to do for someone who has not used VBA before.

A couple of questions:

Do you have any macro's you could share that extract MS project Task ID, Name, Start Date, Finish Date and Duration and place them into excel?

Even if I could get to this end result, how is this any different than using the import wizard. Would this VBA macro just also extract a date from MS Project as text such that I would end up with the same issue as I do now in that I cannot get excel to recognize the text string as a date and format it correctly as a Short Date?

Answer 3

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

Answer 4

Hi Shooter71,

Thanks for using Microsoft products and posting in the community, may I know if you have checked @John's last reply? When you have time, you are welcome to come back and let us know if you still need help here.

Hope you are all well during this period, stay safe and have a nice day : )

Best Regards,

Arck

Answer 5

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?