Category: TFS

How To: Connect TFS-bound Excel to Another Team Project

If you made any changes to TFS-bound Excel Workbook that come pre-installed with your Team Project, you might naturally want to share them with your colleagues who run other projects in your organization. For example, we created a souped-up version of Iteration Backlog that computes our Team Velocity, reports on User Story progress and breaks up work between Developers and Testers. Making these changes available in the Team Project Template is useful for any Team Projects created in the future, but it does not help with existing Team Projects that might benefit from the same improvements. If the changes are significant, it might be hard and time-consuming to edit Iteration Backlog in another Team Project and we would naturally want to just take improved Iteration Backlog and “rebind it” to a new Team Project, in the same or in a different Team Project Collection or even Team Foundation Server instance.

Unfortunately, attempting to reconnect to another Team Project using “Configure Server Connection” option in the Team Foundation Ribbon will not work because it requires the same project collection and it primarily designed to work around backup/restore scenarios:

Configure Server Connection

Configure Server Connection - Error

We will have to apply a workaround solution, which is the following:

1. Save a copy of original Excel Workbook and close it. Disable Team Foundation Add-In. Close Excel.

To disable TFS Add-in, go to File->Options->Add-Ins. Select COM Add-Ins in the Manage drop down and click GO:

Excel - Com Add-Ins

In the dialog that shows up, uncheck Team Foundation Add-In and click OK:

Disable TFS Add-In

2. Open the workbook and remove all custom document properties from the Workbook. These properties contain TFS binding information TFS Add-in will automatically recreate them when reconnecting to another Team Project. Not disabling Team Found add-in seems to allow it to re-create these properties even when saving the document.

This is a bit tricky and tedious, and you might want to use VBA for this. Click Alt+ F11, in the VBA Editor enter, and execute the following Macro:

Sub DeleteCustomProperties()
    Dim p As DocumentProperty
    For Each p In ActiveWorkbook.CustomDocumentProperties
        p.Delete()
    Next p
End Sub

3. Remove hidden verification worksheet. Save and close the Workbook.

This is also done in Visual Basic Editor because that page is hidden from the view. In the Project View in the Visual Basic, select a worksheet with name starting from “VSTS_ValidationWS” and in the Properties Editor change Visible property to xlSheetVisible:

Hidden Validation Sheet

After that, delete VSTS Validation Worksheet in Excel:

Delete Hidden Validation Sheet

4. Re-enable TFS Add-in using the process opposite to the one described above. Open the Workbook again. Notice that when you navigate to the worksheet containing TFS-bound table regions, Team Ribbon does not activate Refresh button because it no longer knows that your workbook in connected to the TFS:

New TFS-Bound List

5. Make sure you do not delete the TFS-bound regions completely because if you do, your formulas will be damaged beyond repair. Keep at least one line from the old table and make sure you copy the name of the table to a text document somewhere – we will need it later:

Name of the Old List

6. Using New List button located in Team Ribbon, add another TFS-bound list that is connected to the same query in the destination Team Project and make sure column layout matches existing list. You will end up with two similar looking lists – one (with one remaining row) from old Team Project and one from the new Team Project:

New TFS List Added

7. Now use the old table name and new table name to adjust all formulas. Verify that formulas work as designed.

One again, this might be easier to accomplish with Visual Basic. Knowing old table name from step 5 and new table name from step 6, run the following macro (it might take a while to execute):

Sub ReplaceFormulas(ByVal originalListName As String,_
                    ByVal newListName As String)
    On Error Resume Next
    Dim w As Worksheet, i As Long, j As Long, _
        formulaValue As String
    For Each w In ActiveWorkbook.Worksheets
        For i = 1 To w.UsedRange.rows.Count
            For j = 1 To w.UsedRange.Columns.Count
                formulaValue = w.Cells(i, j).formula
                If formulaValue <> "" Then
                    formulaValue = Replace(formulaValue, _
                     originalListName, newListName, , , vbTextCompare)
                   w.Cells(i, j).formula = formulaValue
                End If
            Next j
        Next i
     Next w
End Sub

Sub ReplaceAll()
    Dim calc As XlCalculation
    Application.ScreenUpdating = False
    calc = Application.Calculation
    Application.Calculation = xlCalculationManual
    ReplaceFormulas("VSTS_8eeef3b2_74af_457d_88e8_0fce8bc5d131", _
                    "VSTS_d3ffaeb7_2a52_4d3e_afd8_2d7334bb47bb")
    Application.ScreenUpdating = True
    Application.Calculation = calc
End Sub

8. Remove old tables. The easiest way to accomplish this is to first convert it to a Range and then by deleting rows from the Worksheet:

Convert Table to Range

9. Save modified Excel Workbook.

Note: If you have Pivot Table objects pointing to the old tables, you may need to augment ReplaceFormulas macro with updates to PivotTable objects – their Source Data needs to be updated.

Hope that helps!

Advertisements