How to: Reconnect a Microsoft Excel File to Team Foundation Server

  • Article

If you make a change to Team Foundation Server that modifies the URL in some way, the change will break the association between Microsoft Excel files and Team Foundation Server. For example, if either the name of the Team Foundation Server changes or the port number changes, any Microsoft Excel files associated with that Team Foundation Server will become disassociated. This is because the Team Foundation Add-in for Microsoft Excel stores the URL of the server when the file is saved. If the URL changes in any way, the file can no longer be connected to the server. The only way to repair a file is to re-create it.

Re-creating Work Item Lists

The following procedure describes how to re-create a Microsoft Excel file and connect it to the correct Team Foundation Server. The procedure is for the simple case where you have one work item list that is disconnected from the server. If you are recreating a more complex file, you can apply this procedure as a pattern. For example, if you have multiple work item lists, you can follow this procedure to re-create each work item list.

To Re-create a Work Item List that is a list of IDs

  1. Create a new spreadsheet in Microsoft Excel.

  2. Create an empty work item list.

    1. Select the cell where you want to re-create the work item list.

      The cell becomes the upper-left corner of the work item list.

    2. On the Team tab, in the Work Items group, click New List.

      Note

      In Microsoft Office Excel 2003, use the Team menu.

    3. Connect to the correct Team Foundation Server and team project.

      For more information, see How to: Connect to Team Foundation from Microsoft Excel or Microsoft Project.

    4. In the New List dialog box, select the Input List option, and then click OK.

  3. Open the old spreadsheet that is no longer associated with Team Foundation Server.

  4. Create a comma-delimited list of the values in the ID column.

    1. Select the cell that is in the top row of the second blank column to the right of the work item list.

      Note

      Be sure there is an empty column between the cell you selected and the work item list. If the cell is next to the list, the cell will automatically become a part of the list. This will cause the following steps to not work correctly.

    2. Enter a formula that sets the cell equal the ID value that is in the same row.

      For example, if you are in cell E3, and the ID is in A3, enter the formula =A3 in cell E3.

    3. In next cell down, enter a formula that sets the cell equal to the cell above it, plus a comma, plus the value of the ID column in that row.

      For example, if you are in cell E4, and the ID is in A4, enter the formula =E3&","&A4 in cell E4.

    4. Select the range of cells from the last formula you entered to the bottom of the work item list.

    5. From the Home tab, in the Editing group, click Fill, and then Down.

      The formula will be repeated down the list and the cell at the bottom of the range will have the complete comma-delimited list of ID numbers.

      Note

      In Microsoft Office Excel 2003, use the Edit menu.

  5. Use CTRL+C to copy the cell with the complete list of ID numbers to the clipboard.

  6. In the new work item list, on the Team tab, in the Work Items group click Get Work Items.

    Note

    In Microsoft Office Excel 2003, use the Team menu.

  7. In the Get Work Items dialog box, select the Ids option.

  8. Paste the comma-delimited numbers from the clipboard by pressing CTRL+V.

  9. Click the Find button to retrieve all the work items, and then click OK

    The system retrieves the work items and displays them in the work item list.

  10. Select the columns you want to appear in the new work item list.

    For more information, see How to: Add or Remove Columns in the Work Item List.

If there are more than 100 work items in the list, do not copy more than 100 numbers. Break this task into sets of 100 or less work items.

For work item lists created from queries, reopen the query in a new sheet.

To Re-create a Work Item List that is a query list

  1. Create a new spreadsheet in Microsoft Excel.

  2. Select the cell where you want to re-create the work item list.

    The cell becomes the upper-left corner of the work item list.

  3. On the Team tab, in the Work Items group, click New List.

    Note

    In Microsoft Office Excel 2003, use the Team menu.

  4. Connect to the correct Team Foundation Server and team project.

    For more information, see How to: Connect to Team Foundation from Microsoft Excel or Microsoft Project.

  5. In the New List dialog box, select the Query List option.

  6. Click the correct team query from the Select a Query drop down list, and then click OK.

See Also

Tasks

How to: Reconnect a Microsoft Project File to Team Foundation Server

Other Resources

Team Foundation Project Leads