Share via

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

Anonymous
2021-06-10T13:00:39+00:00

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.

Microsoft 365 and Office | Project | Other | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

15 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. John Project 49,710 Reputation points Volunteer Moderator
    2021-07-15T00:25:53+00:00

    Shooter71,

    It turns out I did get you message dated 7/5/21 but for some reason it was in my junk folder which I rarely check. Now that I see it I'll take a look.

    I really prefer to work via e-mail since we are dealing with specific customization for you.

    John

    Was this answer helpful?

    1 person found this answer helpful.
    0 comments
  2. John Project 49,710 Reputation points Volunteer Moderator
    2021-06-14T14:57:23+00:00

    Shooter71,

    I'd be happy to write the code to do what you want. It would be easier than a back and forth to help you make the mods to the existing code. I've done this many times with other users. Contact me at the address below, I will ask some questions. One thing I will need is the path to the Excel file summary report and if possible, it would be helpful to have your Project file and Excel file.

    John

    jmacprojataticlouddotdotcom

    (remove obvious redundancies)

    Was this answer helpful?

    1 person found this answer helpful.
    0 comments
  3. Anonymous
    2021-06-14T02:53:52+00:00

    Hi John, thank you for the continued support. I think my goal has been the same from the first post to my last, but maybe I am not explaining it very well. I believe you correctly captured my goal with your last question in which I have an existing Excel summary report that I simply want to update with Project data when there is a change in the Project schedule.

    So to summarize to be ultra clear of my specifics, I have a MS Project File that contains lets say 400 tasks. Of these 400 tasks, there are 50 milestone tasks (0 day duration with the same start / finish date) which I am tracking in an excel summary report for a task finish date. Anytime one of the finish dates changes in my MS Project File, I want that date in my excel file to update.

    Now I am sharing this detail as the macro currently just pulls all 400 tasks and dumps them into an new excel file. Once I open the new excel file exported by project, I then somehow need to link the data back to my excel summary file.

    You mentioned a couple of options I would have to figure out first before I could make Option 3 really work in which I am not linking my Excel Summary file to the excel file exported out of MS Project for the 50 tasks I am interested in:

    1. Understand how to program the macro to export data not to a new generic BOOK1.XLS file, but point the export directly into my Excel Summary excel file or at least a new sheet within the Excel Summary file.
    2. Link the 50 specific tasks I am interested out of the 400 tasks that are on the MS project file. The 50 milestone tasks are mixed in throughout the MS Project file, so I cannot program say just export the first 50 task for example.

    Do you have any links that can help me figure out these two steps that I would believe are needed to be successful, or any other suggestions on how to accomplish this goal since it appears you are aligned with my overall goal?

    Was this answer helpful?

    1 person found this answer helpful.
    0 comments
  4. Anonymous
    2021-06-13T09:27:01+00:00

    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

    Was this answer helpful?

    1 person found this answer helpful.
    0 comments
  5. Anonymous
    2021-06-10T15:54:48+00:00

    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?

    Was this answer helpful?

    1 person found this answer helpful.
    0 comments