TFS and Excel – Did you know?

One of my favorite integration points for TFS is Office. Seriously! I find excel the most intuitive and natural way to work with my iteration plans, bug lists and perform general task management. Having talked to a few people about this recently it seems that a lot of the TFS and Office integration is going unused, so I decided to start a living post to catalog some of the Q & A I come across to hopefully increase the use of some of my favorite features….

1. Excel as a task entry system

Excel works really well as a list management tool. What people don’t realise is that it can also be used to create lists for publishing to TFS, and it’s really quite simple.

a. Connect to your team project in Excel and get a list of work items from a query (n.b. you can also pick the ‘Input List’ option here which will open an empty list for entry).
b. Note the empty line at the end of the list marked with a *
c. Start entering your data into the empty line, and be sure to fill out all mandatory fields (you’ll get a prompt later if you don’t, so it’s not a show stopper)
d. Once you’re done entering your new tasks, hit publish to push these back to TFS.
e. If you get a prompt regarding required fields, update your data and try publishing again.

That’s it! You’ve just added a list of work items to your Team Project!

2. Excel, TFS and Concurrency

I was doing a pre-presentation review last week, and a discussion started about the Excel and TFS concurrency model. During the discussion I was asked if 2010 addresses the issue of ‘last-in-wins’ concurrency that exists in 2008. This seemed a little strange to me as I wasn’t aware of the issue but I answered that it did – as I had seen the resolution dialogue in 2010.

Having answered the immediate question, I decided that I’d find out exactly what the case was in 2008 as looking at the work item tracking tables I didn’t see a reason that work item version changes couldn’t be identified between fetch and publish in Excel. It turns out that the concurrency model for 2008 behaves the same (on the surface at least) as that in 2010.

The behavior is that when you publish back to TFS a conflict check occurs. If a non-blocking conflict – users have edited different fields in the same work item, is detected an auto-resolution will occur and the publish will complete. If a blocking conflict – users have edited the same fields in a work item, is detected then a conflict resolution dialog will be shown. This dialog allows you to select the change you want to keep, and then to continue with the publish operation.

Conclusion

So, there’s a couple of features around Excel and TFS integration that I’ve been asked about recently. If you have any other questions on any version of the integration (including any for any of the other Office integration points) leave them in the comments and I’ll do my best to get an answer!

Advertisements

10 thoughts on “TFS and Excel – Did you know?

  1. Kai Pinnow

    Hi Steve,

    is there a way to set properties of the Excel export file if you open Excel from TFS? I know that some GUID is stored in the custom section of the Excel properties and we’d like to set some other fields as well.

    Also I wonder if one may configure a certain Excel-template to be used for the export, i.e. for the case that you open Excel from TFS work item list with menu item “Open selection in Microsoft Excel”.

    Thanks,
    Kai

    Reply
    1. Steve Godbold Post author

      Hi Kai,

      I’ve sent you an email to answer what I can, and to get a bit more info.

      Cheers,

      Reply
  2. Marc Delamere

    I’ve created a new WIT where the workflow starts off New–>Assessed–>Active. When I change the state from New to Assessed in TFS there is no problem. But when I edit the state after exporting to Excel, the “Priority” field also changes (in fact back to it’s original value). New requires the Priority to be set, but the transition from New–>Assessed does not mention Priority.

    Would you have any idea why the priority field changes when I only change the state – and there’s no rule or workflow trying to do this?

    Thanks, Marc

    Reply
    1. Steve Godbold Post author

      Marc,

      If you could send me your WIT XML I’d be happy to take a look. You can get me at stephen.godbold at gmail.com

      Cheers,
      Steve.

      Reply
  3. Rob Priles

    Hey Steve,
    I hope there is an easy way to do this but I don’t think there is … once you have created your spreadsheet with all of the fields you want to work with, can you save that as a template to be used for other Projects? I can’t figure out a way to go back and change the Project connection once it has been established for the initial project. Every time I select New List, it goes straight to the Query List or Input List option without giving me the ability to select a different Project. Basically, I just want to be able to change from one project to another … maybe manage multiple projects on different tabs or just be able to create a template that I can use for different projects using different spreadsheets. Any option will work 🙂

    Thanks for your help!
    Rob

    Reply
    1. Steve Godbold Post author

      Hi Rob,

      Unfortunately no – once you’ve bound the workbook to a project you’re stuck. In 2010 you can change the project using the Configure -> Server Connection option on the team tab, but this will only work if you’ve got an empty workbook. I believe there has been some discussion around enabling the scenario you describe but at this time I would say your best bet is to do as much as you can in the query, then copy the query between projects.

      Cheers,
      Steve.

      Reply
  4. Sthanu Subramaniyan .P

    Can we also integrate MsWord with TFS. If that possible please describe how. If not please describe the reason?.

    Reply
    1. Steve Godbold Post author

      Hi Sthanu,

      What kind of integration are you after? There is some levels of integration, including the ability to generate documents from testing artifacts.

      Cheers,
      Steve.

      Reply
  5. Nikki

    Hi Steve. I wanted to let you know how much I LOVE the integration with Excel. It’s a life saver. I have created pivot tables based on my queries to serve as my e-Kanban board for all of my active projects. What I wish could happen is the TFS queries would automatically refresh when the Excel file is opened. I have set my 9 excel files to open before I get in via a scheduled task, but I have to manually update my queries which is laborious.

    Reply
  6. Greg Hubert

    Hi Steve,

    I have some workflows that I have developed using Excel and TFS. For me it is so much easier to track my iterations, developer load, bug list and just about everything via Excel.

    I am actually wondering the exact same thing as Nikki posted above. I cannot find a setting to automatically update Excel when it is open to the latest TFS data, so I am trying to figure out how do write some custom VBA to do this. Any insights you would have to enable me to do this would be greatly appreciated.

    Thanks
    Greg

    Reply

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s