create-and-close-tfs-using-excel.vba

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