none
Link Project to Excel on SharePoint Sites RRS feed

  • Question

  • I am trying to figure out a way to link information from MS Project into MS Excel using files that are stored on SharePoint 2010.

    I can create a link that will update between Excel and Project by doing copy, paste special - link (text), that will update appropriately when the files are stored on my local computer. When I try to do the same thing with files that are stored on SharePoint I get #REF errors in Excel where the links reside.

    What is the solution to be able to connect these programs? If it isn't the paste special is there another way that I can have information stored in MS Project files automatically update in an Excel file with EVERYTHING stored on MS SharePoint?

    Friday, December 4, 2015 9:13 PM

All replies

  • Susan,

    I can't help you with the SharePoint tie in but I will caution you about using paste links to get Project data synced with Excel, or any other application. Basically, don't do it. Paste links are very fragile and subject to corruption.

    As far as automatic update of Excel with Project data, it can be done with some VBA, perhaps using an Event macro on open or close of the file. Something could also be set up to update "on-the-fly" but that type of Event macro is more complicated.

    John

    Saturday, December 5, 2015 3:43 PM
  • John,

    Thank you for the reply. I am not familiar enough with VBA to create the macro that would be needed. Would it be difficult to create the connection via VBA to the Project files? Do you know of a resource where I could get assistance building the connection?

    Susan

    Monday, December 7, 2015 2:02 PM
  • Susan,

    Yes, I can give you a couple of resources for Project VBA. The following Wiki article addresses transfer of Project data to Excel using VBA. It was created specifically to address the truncation of Notes field text but the basic macro code can be expanded as needed.

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

    For a more complete resource on Project VBA, I recommend Rod Gill's book on the subject. You can find out more about the book at:

    http://www.project-systems.co.nz/project-vba-book

    John

    Monday, December 7, 2015 3:32 PM