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

7 comments

  1. Rob Laddish

    Thanks! We also experimented with this and do it slightly differently. You might want to try this process, as I think it’s a little cleaner.

    – Turn off the TFS ADDin
    – Delete all the Validation sheets.
    – Delete the query table (list)
    – Delete all custom doc props that match VSTS or VS
    – Delete all hidden defined names associated with VSTS
    – Save the workbook
    – Re-enable the TFS Addin
    – Exit without saving.

    Now restart the workbook, and use the team ribbon to pick the new project and the new query. Voila!

    • Dmitry Frenkel

      Rob,

      This is a fine approach too – the problem, however, is that if you have formulas in your workbook that refer to the TFS worksheet and TFS region, picking new project and new query is going to clobber that TFS region/table thus breaking all the formulas anywhere in your worksheet. In my case, I needed to avoid that at all costs.
      If, however, you just want to look at the data and no custom formulas are needed, your approach would work fine.

      • Rob

        Hi Dmitry,

        Ah, now I understand your approach better and the constraints you are under. 🙂 You are right, we don’t have custom ranges pointing to the TFS validation data sheet, we just use the query data (refresh/edit/publish). FYI, I’ve noticed a couple interesting things in reverse engineering the process:

        a) On the Validation sheet, sometimes a refresh will cause the Addin to “shift the columns” to the right. It looks like columns 5-20 will become empty, then columns 20-35 (or so) will contain a new set of data. The refresh time also spikes upward for this operation. Hopefully when this occurs, it does not break your custom named ranges.

        b) I’ve recently noticed that one of my workbooks takes 20 seconds just to save it. It appears the TFS Addin is taking ~15 seconds to dump its internal data structures into Base64 text in the documents custom properties. I found that if I turn off calculations, this 15 seconds process speeds up to <1 second. Wow! Unfortunately, the BeforeSave event I created gets called after the TFS addin BeforeSave. I am looking to create another event that gets called before the TFS Addin so I can turn off calculations, then turn them back on during and AfterSave event. Have you looked at this at all?

        c) TFS2010 ships with 2 workbooks, one is product backlog and the other a sprint backlog. (they use different names) I've found having them both open at the same time leads to reliability issues with the TFS Addin, it often disconnects one of the workbooks or crashes excel. Bleh. For now, I enforce having just 1 workbook open at a time. Have you run into this issue or found any workarounds?

        Thanks,

        Rob

    • Dmitry Frenkel

      That depends on the amount and complexity of the customizations. The main problem is that starting the new unbounded workbook and recreating all TFS tables there will give you different table names, hence forcing you to correct EVERY formula that you’ve built. This is precisely why it is desirable to keep the table names and make sure they are just connected to another TFS. As painful as it is, it is far better than fixing long formulas (and introducing bugs).

  2. Eric W

    Will the method described above allow me to have multiple worksheets in one workbook that are all connected to a different team project? I was trying to build one excel document that had multiple worksheets available to pull a query from each of about 10 team projects. But, when I tried to copy the query worksheet into another excel document it lost the connection to TFS.
    Thank you for any assistance you can offer.

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