TFS 2010 CTP: Adding customized Excel workbooks to process template RRS feed

  • Question

  • Hi everybody,

    I am playing around with the TFS 2010 CTP. I love the feature to customize Excel workbooks, insert formulas and additional excel columns and the possibility to save the changes in the workbook. Now I would like to create my own customized workbook similar to the product/iteration backlogs and add them to the agile process template.

    I tried to download the agile process template and found the templates for the product/iteration backlogs, but when I open them, there is only a white space, where the work items should. I suppose this is because the template is not connected to a TFS project.

    Is it possible to create an excel workbook template, which is automatically connected to the TFS project I just created? It should be, because the product/iteration backlogs contain this functionality. If it is possible to create such an Excel workbook template, is it also possible to add excel columns to the workitems list, which are not connected to the TFS. This columns should only do some addtional calculating just in Excel.

    The goal I try to achieve is to create an Excel sheet for estimation. We use PERT and some other formulas to calculate estimations and I want to do this in Excel. The TFS should only get the final estimated value, after some formulas calculated it in Excel.

    I hope anybody can help,
    • Moved by Bill.Wang Thursday, April 30, 2009 10:00 AM Move to the correct forum. (From:Team Foundation Server - Process Templates)
    Monday, April 27, 2009 9:57 PM

All replies

  • Hi wuschl

    I'm moving this thread to Visual Studio Team System 2010 CTP Feedback Forums.
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Send us any feedback you have about the help from MSFT at fbmsdn@microsoft.com.
    Thursday, April 30, 2009 9:59 AM
  • Here is a quick overview of how we customize the workbooks that go into the process template.  We do all this work without ever binding the workbook to an actual solution... so essentially we build it "disconnected" and then let the Project Creation take care of binding it to the project.

    • Right click on the Iteration Backlog query in Team Explorer and display it in Excel.
    • Select the query results table (including the header names) and copy it into notepad.
    • Save the file as a text file.
    • Copy the text from notepad onto the first sheet in Excel. You don’t want to copy directly from the other workbook because this will create a reference between workbooks, which is why you want to paste in the text from notepad.
    • While this text is still selected, insert a table so that the copied text becomes a table.
    • Open the VBA editor and modify the Prepare script in the Cleanup module so that it has the correct names for your cube server, etc.
    • Run this script by pressing F5 when the cursor is inside the script.

    At this point you can make changes you want. When you’re done making changes, run the Cleanup script to put the workbook back into a “clean” state so it can be include in the process template.  The Office Integration add-in adds some extra properties and hidden worksheets that “bind” the workbook to a specific TFS instance and team project when the team project is created. Workbooks included in a process template need to be unbound because they’re bound to a TFS instance through a combination of project creation and opening the workbook (at which point the OI add-in kicks in and does some additional work).

    Monday, May 18, 2009 7:24 PM