none
Round trip data from Project to Excel and back again? RRS feed

  • Question

  • I found this:

    http://projectserverblogs.com/?p=2128

    and that seems to be as good as it gets. Is it? I am more interested in roundtripping just tasks, but i bet that's not possible. What say you? thx. 


    Jiggy Gaton, Trainer and Media Consultant for Nepal and S. Asia


    • Edited by Jigs Gaton Thursday, August 1, 2013 12:54 PM
    Thursday, August 1, 2013 12:53 PM

All replies

  • Hi all, need help bad on this one: I want to create an excel spreadsheet that I can distribute to task managers for data gathering of tasks. I've done this many times before in Project 2003-2010, but now we are using Project 2013. My old technique of exporting the spreadsheet and then merging the sheet back in now produces a merge where all the rolled up summaries are changed to manual mode. huh? see the screenshot below. Is this a bug, or perhaps something has changed? 


    Jiggy Gaton, Trainer and Media Consultant for Nepal and S. Asia

    Thursday, August 1, 2013 11:02 AM
  • Herojig,

    Can't help you with Project 2013 but it would be of benefit for others who answer to know what you mean by "rountripping" just tasks. That seems to be exactly what the referenced blog is detailing.

    John

    Thursday, August 1, 2013 3:00 PM
  • yes john, the blog references roundtripping task details (percent complete, start, stop, etc.) but I want to a method that will allow for a task manager to add / subtract entire tasks. the blog says that can't be done as if you key off the task ID, and then delete or add tasks, it will get out of sync and not even import. 

    I am working with a  large group of users (task managers) and we are trying to put a large plan together, and I am hoping to find a way to automate the process - giving them something to fill in, but under a pre-determined framework. For example, I have already created a master plan and sub plans. The subplans are what need to be created and then updated. 

    Here is what I thought I could do, but the part about excel spreadsheets is not working out so far:


    Jiggy Gaton, Trainer and Media Consultant for Nepal and S. Asia

    Thursday, August 1, 2013 5:18 PM
  • Herojig,

    The import wizard has three options - new project, append and merge. The blog describes how to properly use the merge function and that is designed for updating existing tasks in Project with new data. If you want to add tasks then the append option is what you need, but as the blog writer stated, added or deleting tasks will nullify any syncing with the ID number but you could still use the Unique ID however.

    Deleting tasks is a different matter. As far as I know, no type of "import" will delete tasks. How do you import nothing?

    In my view if you want the most flexibility for updating the files, VBA is the way to go. It doesn't have the limitations associated with an import/export map. For those task managers and perhaps other partners without MS Project, their data entry could be to a pre-formatted Excel template. The gatekeeper would then run an import macro to bring the updated template data into the project plans.

    John

    Thursday, August 1, 2013 8:26 PM
  • In my view if you want the most flexibility for updating the files, VBA is the way to go. It doesn't have the limitations associated with an import/export map. For those task managers and perhaps other partners without MS Project, their data entry could be to a pre-formatted Excel template. The gatekeeper would then run an import macro to bring the updated template data into the project plans.

    John

    Yes, that's what I want! So no one has created this macro yet? I have no experience with VBA macros :( well, thx for the input!

    Jiggy Gaton, Trainer and Media Consultant for Nepal and S. Asia

    Thursday, August 1, 2013 9:24 PM
  • Herojig,

    I don't know. I'd developed many macros myself, but none that do just what you need. You might consider learning Project VBA yourself and I can suggest an excellent book to get you started on your way - Rod Gill's book on Project VBA. You can find out more about it at: http://www.projectvbabook.com

    Good luck with your process.

    John

    Thursday, August 1, 2013 10:52 PM
  • Its something I find myself doing more and more. My preference is to "Publish" the project to SQL Server weekly and then to round trip between Excel and SQL Server then update Project from SQL Server.

    This is more flexible and scalable. I have a number of clients doing timesheets in Excel for Project, or just task updates or a variety of different flavours. So far, each client has had different needs otherwise I would have a great shrink wrapped app to sell!

    So unfortunately there isn't a macro to do exactly what you want, but there is a toolkit of small macros that can be stitched together for your solution. My book has most of them! Thanks for the mention John.


    Rod Gill

    The one and only Project VBA Book

    Rod Gill Project Management

    Friday, August 2, 2013 8:48 AM
    Moderator