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:
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:
In the dialog that shows up, uncheck Team Foundation Add-In and click OK:
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:
Dim p As DocumentProperty
For Each p In ActiveWorkbook.CustomDocumentProperties
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:
After that, delete VSTS Validation Worksheet in Excel:
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:
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:
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:
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 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
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:
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!