Loading...
Quick Links

Slick Trick: A Spin on Sync’ing with SharePoint

Situation

Microsoft Project 2010 now has the capability to sync a project plan with a SharePoint task list.  Therefore, project team members do not need to have a desktop version of Microsoft Project in order to view project tasks.  This is great functionality – except in one area.  If the project you are sync’ing with SharePoint has a WBS that includes multiple outline levels, the SharePoint list will convert all summary tasks into a hierarchical folder structure.  Then you need to navigate throughout the folder structure in order to see all your project tasks and corresponding Gantt charts.  There is also an option of creating an additional view that allows you to see all tasks without folders.  However, this will only give you the ability to see non-summary tasks.  You will get a Gantt chart of all these specific tasks but no outline breakdown in the tasks themselves.

I have a slick trick to help out with this problem.  I have developed a macro that you can run on your project plan in MS Project to reformat your plan a bit prior to sync’ing with SharePoint.  Then, sync your project with SharePoint and you can see your whole project in SharePoint with a full outline structure and Gantt chart!  Then when you sync back to MS Project from SharePoint, you can run another macro to reformat the project back to its original form.

These macros are simple and extensible and include some not-so-obvious tricks.  Let me show you this in action!

Here is the initial Project Plan – subset of one of the standard Microsoft Project templates

Sync a Microsoft Project Plan with a SharePoint Task List

Sync the project with SharePoint

All the summary tasks are converted to folders and you need to navigate through the folder structure to see all the tasks.  No total view of your project!

Sync a Microsoft Project Plan with a SharePoint Task List

 

Create a View Without Folders

A View Without Folders shows you only the non-summary tasks.  So still not a total view of the project!

Sync a Microsoft Project Plan with a SharePoint Task List

 

My Trick

Run the following macro, ChgTaskName, on your project plan:

Sync a Microsoft Project Plan with a SharePoint Task List Macro

 

The original project reformats from this:

Sync a Microsoft Project Plan with a SharePoint Task List

 

To this:

Sync a Microsoft Project Plan with a SharePoint Task List

 

There are some differences (i.e., auto scheduled converted to manually scheduled tasks) but for all appearances, the plan looks the same.

Now Sync the Project with SharePoint

The SharePoint list created from your project plan now looks like this:

Sync a Microsoft Project Plan with a SharePoint Task List

 

Outline structure is maintained and there are no folders.  Now you can get a total view of your project!  Your team members can easily see the full project in all its beauty.   How cool is that!!!

 

Lastly, at some point you will want to reformat the project back to its original form.  There is another macro for that process.

Now run the following macro, ChgTaskNameBack, on your project plan:

Sync a Microsoft Project Plan with a SharePoint Task List Macro

 

Your project reformats from looking like this:

Sync a Microsoft Project Plan with a SharePoint Task List

 

To now looking like this:

Sync a Microsoft Project Plan with a SharePoint Task List

 

Your plan is reformatted back to how it looked initially, auto scheduled tasks and all!

 

All this from 2 simple macros!  As I said before, these macros can be extended to handle more complex situations.  (And I’ll tell you a little secret about these macros that may not be obvious.  The macro code appears to be adding and removing spaces to the Task Names in order to preserve the outline structure.  Those aren’t spaces.  They are actual non-breaking spaces that you can copy from Windows Character Map application.)

Good luck!

Jean Lieverman
Written by Jean Lieverman

Jean Lieverman is an experienced project management consultant who enjoys sharing her knowledge with others. She has created project and program management systems and has expertise in pharmaceutical R&D. A PMP, she has been active in several PMI® groups as well as MPUG. Additionally, Jean has an MPH in Biostatistics, MS in Bioengineering, and BA in Psychology. Contact her at jean@lieverman.com.

Share This Post
18 Comments
  1. Hi Jean,

    Quite cool. However, I note that you don’t have any error handling in your code.
    If the plan has a blank line (which people often do) it might hang.
    Any time I iterate through all the tasks in the project, i do this checking…

    For Each tsk In ActiveProject.Tasks
    If Not tsk Is Nothing Then
    …..your code here
    End If
    Next tsk
    End Sub

    I will implement your code.

    Thank you for sharing!

    Reply
  2. Jean, that is very cool and makes the SharePoint Sync scenario a lot more attractive!

    Reply
  3. Good stuff!

    It would be nice to empty the temporary fields back to nothing after re-instating the values for completeness.

    Reply
  4. Nice trick, Jean! It appears to be very useful for those who don’t have Project Server. I hope to see many more articles like this in the future.

    Reply
  5. Very impressive.
    It works really well. But does anyone know how to save the formatting changes in SharePoint? Specifically setting or changing the default column width for the description, and rendering a zoomed out view the whole gantt chart when it first opens?

    Reply
  6. Jean Lieverman

    Hey thanks for all the comments. Y’all were right, I didn’t show any error checking code. Routines like this should have error checking – good catch. I’ve learned this the hard way! Just didn’t want to make the code so long for this example. I also liked the idea of clearing the temp fields. In any case, thanks for reading my article and for the good suggestions.

    Reply
  7. The macro runs fine in Project ant reformats everything, but when I sync to SharePoint I still lose all the indentation. Is there a particular setting that is needed in SharePoint regarding folders of some other setup. Using Sharepoint 2010, Project 2010, IE 8. This is very helpful but maintaining the indent like you have would be even better. Thanks

    Reply
  8. Hey Jean, nice work with that one.

    I was wondering a way to work around this and ended up additional columns to the project plan in MS Project with repetitive values and then adding group functionality in the SharePoint list… real pain… but it works… not as good as yours though.

    Now, I have a question if you can help me figure this one out would be so awesome. I know that whenever you mark a task as milestone in MS Project this task will show as a single point in the SP list once is sync’ed. However, I am in the need to create a view on the Project Task list that will display the milestones only.

    How do you do that? list only the milestones in a different view.

    Thanks in advance…

    Reply
  9. HI,
    Is there a way to show even the Project Summary Task (Line 0) also when we are syncing a MS project file with SharePoint?

    Reply
  10. Hi Jean,

    This is GREAT!! THANK YOU.

    I’m having the same issue as John Budzynski (on September 10, 2013 at 4:45 pm):

    The macro runs fine in Project ant reformats everything, but when I sync to SharePoint I still lose all the indentation. Is there a particular setting that is needed in SharePoint regarding folders of some other setup. Using Sharepoint 2010, Project 2010, IE 8.0.7601.17514CO. This is very helpful but maintaining the indent like you have would be extremely helpful.

    Thanks.

    Reply
  11. So for anyone who is having issues with the Gantt formatting i.e. the indents are removed from the Gantt view. It is because when you copied the macro code, you placed a space ” ” where a non-breaking space should have been used.

    In order to place a non-breaking space replace ” ” with Chr(160) e.g.

    pfix = String(num * 5, Chr(160))

    and not

    pfix = String(num * 5, ” “)

    This should fix the problem and is needed for both macros.

    Reply
  12. H jean:
    Nice job, that’s useful.
    But I have problem to sync my SharePoint server with desktop.I mean that if I want to set an alert on desktop which shows any one’s current task (except using MS Outlook),How can I do this?
    I want to run a program that without using MS Outlook my colleagues can see their tasks from sharePoint on their desktops
    Regards

    Reply
  13. This looks like it is a really great code to make the sync work. This post is a little old so I’m not sure if I’ll get a reply or not. I’ve entered the code exactly into VBA and run into a problem where I’m getting the error “Run-time error ’91’: Object variable or With Block variable not set”. When I go into Debug it looks like ti’s the tst.Start10 = tsk.Start line (highlighted yellow). I’m kind of stumped here why it’s not working. I had a coworker review it too and the code is entered correctly. He was thinking that Task isn’t actually a function in my version of Project and perhaps there was an add-in you may have had to make this a function?

    Thanks for your help!

    Reply
  14. oh, just to clarify as I had a typo above, the error is on code tsk.Start10 = tsk.Start.

    Thanks,

    Brent

    Reply
  15. I just upgraded to Project 2013 Professional and the indent/outdent functions exist there. No need for VBA workarounds!

    Reply
  16. When running the code, I receive the error, object required. I copied the code exactly the way it’s presented in the post. Can anyone assist with this?

    Reply
  17. Thanks for this Jean. Have you ever looked into automating the synch process with a scheduled task? We would like to automate it so end users or admins don’t need to open Project to do the synch.

    Thanks.

    Ed

    Reply
  18. My colleagues required a form some time ago and were informed about a company that hosts lots of fillable forms . If you require it too , here’s https://goo.gl/EtB0FJ

    Reply

Leave a Reply

Your email address will not be published.

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

Please complete this equation so we know you’re not a robot. *

Thanks for submitting your comment!
You must be logged in to comment.