TFS 2010 Iteration Backlog Workbook and Macro Execution Warnings

March 07, 2010

Now I’m not an Excel genius or anything so if I’m doing this wrong, let me know.  Anyway, I went in TFS2010 to play around with the TFS2010 Agile Iteration Backlog workbook (Iteration Backlog.xlsm) and I ran in to some problems with Excel macro execution.  The Iteration Backlog workbook helps you to plan out the work in your Agile Iterations (or Scrum Sprints) using historical data from TFS about previous sprints and items in your product backlog.  In order to do it’s magic, the worksheet needs to be able to execute its macros.

To open the iteration backlog workbook, open up Visual Studio 2010, go to Team Explorer, and find your Team Project.  From the Team Project, drill in to Documents –> Shared Documents –> Iteration 1.  Now you should see Iteration Backlog.xlsm.

image

If you double-click the document, you’ll see the following File Download warning.

image

Hit OK.

Excel should pop up and you should see a Server Workbook warning and a Security Warning.

image

According to the current version of the documentation, you should be able to click the Options… button on the Security Warning to enable the Macros.  But instead -- at least on my machine – Excel goes in to a “hourglass” waiting mode and then I get a TF208085 error from TFS saying that “Office Excel has disabled macros in the workbook.  To recover the macros, save and re-open the workbook”.

image

Hmm.  Well, I don’t really want to do that.  Click OK on the Excel message box and then POOF! the Security Warning’s Option button disappears.

image

At this point, you may be wondering “well, are the macros really disabled?”  Yes.  Totally disabled.  In case you’re wondering, click on the Capacity worksheet tab at the bottom of the Excel window.

image

You should now be on the Capacity worksheet and you should see a bunch of Macro warnings.

image

(Doh!)

Here’s how to enable the macros.

image

Click on the big button in the upper left corner of Excel.

This should bring up a menu like the next picture.

image

Click on Excel Options.

You should now be on the Excel Options dialog.

image

Select the Popular tab page. 
Check **Show Developer tab in the Ribbon.
**Click the OK button.

The Developer tab should now be visible in Excel.

image

Click the Developer tab and locate the Code section of the tab.

image

Click Macro Security to bring up the Trust Center dialog.

image

Select Enable all macros (not recommended; potentially dangerous code can run).  (NOTE: this is the part where I’m wondering if there’s a better way to make this work.  That warning on the radio button option is sounds pretty dire.  If you know of a better way, please let me know.)

Click OK.

Now that you’ve changed the Macro Settings, you need to re-open the Excel workbook.

Close Excel and re-open Iteration Backlog.xlsm from Team Explorer.

Now the macros should be working.

-Ben

Categories: team-foundation-server-2010 visual-studio-team-system-2010-vsts2010
Tags: alm