So I use TFS 2010 at work for source control. My team also uses it for task management. Every day we put in everything that we did that day. It's not a big deal for us if the times are correct, but we just want to get the tasks created on the day they were put in so we can report on what we got done by day.
One of the gaps with using TFS in VS 2010 or with some of the products that integrate with it is that you *have* to open a task, and then close it. you can't just create a task called 'talked on phone to person x about subject y' mark it as 15 minutes of time and then close it. you have to create the task, then save it, then close it. not a big deal, but ultimately a bit of a pain every time you record work. Any pain during task tracking is, IMO, a real barrier to entry. It's already difficult enough to get people to record their tasks. =P
Anyway, on to the code. I wrote a macro that uses a list in excel hooked up to TFS and it allows you to just put in your tasks and then update some data row by row based on what you have put in and then it 'saves, closes, saves' for you. i wasn't able to find an example of how to do this on the web anywhere, but i found all the components i needed as seperate references. those references are listed below in the code.
Sub MyMacro()
'how to select adjacent cells - http://www.google.com/url?sa=t&rct=j&q=excel%20select%20adjacent%20cells%20vba&source=web&cd=1&ved=0CFoQFjAA&url=http%3A%2F%2Fwww.ozgrid.com%2Fforum%2Fshowthread.php%3Ft%3D89576&ei=iiLNT_bMHqmg2gW1o9yIAg&usg=AFQjCNEia4-TwdJPRit8MKRh69ITAEKpJg
'how to work with the table object - http://www.jkp-ads.com/Articles/Excel2007TablesVBA.asp
'how to loop over the cells in a range- http://www.vbaexpress.com/kb/getarticle.php?kb_id=563
'how to work with only the visible cells - http://www.mrexcel.com/forum/showthread.php?t=66490
'how to click the publish button - http://social.msdn.microsoft.com/Forums/en/tfsgeneral/thread/af266d3f-c288-41c1-8611-97994545afc6
'how to get the name of the current list-http://www.pcreview.co.uk/forums/get-name-active-list-t3872389.html
Dim t As String, w As Worksheet, col As Range, cell As Range, pb As CommandBarControl
Set pb = Application.CommandBars.FindControl(Tag:="IDC_SYNC") 'grab the publish button
If pb.Enabled Then 'make sure the publish button is enabled
t = ActiveCell.ListObject.Name 'get the current list name
Set w = ActiveSheet 'get a reference to our sheeet
Set col = w.Range(t + "[[State]]").SpecialCells(xlCellTypeVisible)
'above, get a reference to the column we want. only select the visible cells
'set the defaults and then publish
' for each cell in the column, go through each and
' since it's blank, i know it needs to be updated
' i then
' * set it to active
' * set the type of item to 'task'
' * set the reason to new
' * set the assigned person to me
' note:
' these could be any cells or values, i am using my own
' offsets relative to the cell i am checking for the blank value.
For Each cell In col
If cell.Value = "" Then
cell.Value = "Active"
cell.Offset(0, -1).Value = "Task"
cell.Offset(0, 1).Value = "New"
cell.Offset(0, 2).Value = "Ashbrook, Roy"
End If
Next cell
pb.Execute 'press the publish button
'close the tickets and re-publish
'same as above, except that this time i mark them closed and completed.
For Each cell In col
cell.Value = "Closed"
cell.Offset(0, 1).Value = "Completed"
Next cell
pb.Execute 'press the publish button
End If
End Sub
posted @ Thursday, June 14, 2012 11:20 AM