TFS and Excel – The Basics

Flicking through my blog stats on a Friday shows an interesting trend. On Friday my most common search term is generally “connecting to TFS in Excel”. This tells me 2 things – lots of reports are being written on a Friday, and there’s some demand for a starter guide for TFS and Excel integration!

First things first – licensing. The licensing requirements to connect to TFS from Excel and generate reports is fairly simple. You need a CAL. The people you distribute your reports to as either an xls(x) or hard copy won’t require a CAL, with the restriction that they can’t access the server to refresh/update/create data. For more info on licensing you can get the appropriate white papers for 2005 from here, and for 2008 from here.

Right, now we’ve made sure we’re covered by the right license we need a way to connect to TFS.

1. Install Team Explorer

If you’re using 2005, and have the TFS install media you can find it in the TFC directory of the media. Don’t have access to the install media? It’s also available for download from here.

If you’re looking for the 2008 version, you can get it from here.

The downloads both come in the form of an ISO, so you’ll need some kind of ISO reader. WinRAR and 7-zip will both let you extract what you need to do the install. Once you’ve got the files out, it’s a matter of following the bouncing ball through the setup wizard.

2. Open Excel

Now we’ve got a way to connect to TFS, the next step is to open your flavour of Excel. If you’re working with 2003 you should see a toolbar similar to the image below:

Excel 2003 Team Toolbar

Excel 2003 Team Toolbar

If you’re using 2007, you’ll get a ribbon that looks like this:

Excel 2007 Team Ribbon

Excel 2007 Team Ribbon

If you don’t see one of the above, check your install to make sure it completed successfully. You should see an item in your add/remove programs that looks a bit like this

Team Explorer

Team Explorer

–Update–

If you’re running Office 2007 you can also check the status of the add-in in Excel. Instructions on the WIT Tools blog here.

3. Get a list of work items

To get the list of work items you’d like to work with you’ll need to click the ‘New List’ button in the toolbar installed with Team Explorer. Depending on your configuration, you might get a credential prompt at this point. If you do, enter the credentials that you usually use to connect to TFS (if you’re unsure, best to contact your TFS admin to find out which set you should be using).

You should then see a dialog asking you to pick a server, and team project to connect to. You’ll need to specify a server if you haven’t already. You can do this by clicking the servers button, then the Add… button.

Connect to TFS Server

Connect to TFS Server

Finally enter the URI and the configuration used to connect to your TFS instance. Once again, if you’re unsure – best to contact your TFS admin for these values.

Once you’ve set up a server, pick the team project you’re interested in reporting on and click OK. Double clicking the team project also works.

You’ll now be prompted to pick a query that will form the basis of your work item list. If you like, you can also pick the ‘Input List’ option to enter work items. The queries listed are those specified against the team project you selected. This means if none of those presented suit your needs and you’re not that keen on re-filtering every time you open excel you can go define a new work item query that you can use later to generate lists.

Once you’ve picked a query and clicked OK, Excel will head off to TFS and start building your list. From here on in you’re in Excel land and can generate pivot tables, charts and filtered lists all based off the data you’ve extracted. You can also add work items by entering data into the blank row at the bottom of your list that is denoted with an asterisk.

If you are adding new data – or updating items via the Excel list, you’ll need to hit the publish button to push this data back to TFS before it’s in the system though. An important thing to note here is that if you refresh your data set before publishing any changes you’ve made will be lost. This also applies to any custom formatting you’ve applied to the work item list (though this scenario is addressed in the 2010 integration model).

Well that’s about that. A basic guide to getting a work item list from TFS in Excel that will hopefully simplify the reporting story that most of us go through at some point.

Advertisements

5 thoughts on “TFS and Excel – The Basics

  1. Pingback: TFS and Excel – The Basics « Steve Godbold

  2. Mathias

    Hi,

    (spell checked version)

    If I add a TFS list to an Excel sheet and make a pivot table based on that data everything works great except updating the data.

    I have an Excel workbook with the TFS list on one sheet and a pivottable summarizing scenarios per iteration on another. To update the data (in case someone else has updated the scenarios in TFS etc) I need to go the the Data-sheet, selected the Team-tab, press Refresh, go to the Report-sheet, select the Data-tab and press Refresh to update the pivot.

    Do you know if/how I could trigger the tfs list updating from the Data-refresh or is there some other one-button solution?

    Reply
  3. mike

    I find that I love and hate this plug in – it’s so versatile and helpful, but at the same time it can be such a finicky dog. embedding a few queries into a spreadsheet for reporting purposes often leads to looooong wait times to get connected to the server. sometimes, though it’s quick. there seems to be no rhyme or reason what the response time is going to be, but it’s not network issues because running the same query in the IDE is quick and easy.

    any ideas why the ‘connecting to server’ message can last minutes to nearly an hour before the connection is established and the Team menu funcitons?

    Reply
  4. Greg

    I am having issues where I have connected to TFS from excel. It works pretty well for me. And then all of a sudden the connection is lost. I open up Excel and I only have the option to create a new list. We have seen this many times over the last month.

    Has anyone else seen this issue, and if so have you found a way to fix it?

    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