none
VBA connect to TFS - Excel Automation of Work Item Creation

    Question

  • Is it possible through VBA code within Excel to connect to TFS and create work items and alerts? If so, could someone please provide sample code that connects to TFS and creates a work item.

    The ultimate end goal is to create an Excel template form which allows users to fill in some fields with data and then press a button which would connect to TFS and create multiple work items and (wishful thinking here) alerts based on the user entered data. Upon pressing the Submit button the desired results are something like this:

    1. A new Issue is added under an existing Requirement in TFS

    2. The Requirement is updated (which triggers an existing Alert for any changes to this Requirement)

    3. A new Task (Task 1) is created as a child of the Issue created in Step 1.

    4. A new Alert is created for Task 1 when state changes to Active

    5. A new Task (Task 2) is created as a child of the Issue created in Step 1.

    Thanks! Any and all feedback/questions/suggestions are greatly appreciated.

    Tuesday, February 14, 2012 4:15 PM

Answers

All replies

  • Check this post http://www.codeproject.com/Articles/183317/Update-TFS-WorkItem-from-Microsoft-Access-using-VB, it might help you.


    Please remember to mark the replies as answers if they help Thanks, --Kuldeep

    Tuesday, February 14, 2012 4:25 PM
  • I have looked at that posting more than a few times but that requires the creation of a new C# Class Library and two TFS DLL references. I'm looking to do this from VBA within Excel without needing to create additional files.
    Thursday, February 16, 2012 2:03 PM
  •  

    Hi Tman, 

    Thanks for your post.

    If without create the Class Library and TFS dll reference, I think we can’t connect to TFS using VBA. 

    Could you tell us why you want to do that from VBA within Excel without create additional files?

    We can install the Team Explorer 2010, then connect to TFS 2010 using the Team tab in Excel.    


    John Qiao [MSFT]
    MSDN Community Support | Feedback to us

    Friday, February 17, 2012 7:59 AM
  • Hi John,

    I have the Team Explorer installed but there are a few reasons why I want to do this entirely using VBA code. The first issue is that I would be providing this form template to end users and the Team Explorer doesn't connect automatically so the end user then has to click on the Team ribbon, then click configure, and then click Server Connection and manually make the Excel connect to TFS. There are a variety of postings about this issue and even VBA code for how to make it auto refresh (which only works after you do the above steps to establish the connection to TFS) and do other things by the code basically pressing buttons on the Team ribbon.

    The goal is to give this single Excel file to about a dozen people and make it painless for them to fill in a few fields and create all the needed items/alerts in TFS. The personnel using the form do not really use TFS and are not developers so asking them to go in and make all the items and alerts in TFS is unrealistic whereas giving them a form and telling them fill it in and press the button is acceptable. However, so far I have been unable to find any VBA code to connect to TFS but I am confident it must be possible.

    Thanks,

    Tim

    Friday, February 17, 2012 2:24 PM
  •  

    Hi Tim,  

    Thanks for your reply.

    As we know that, TFS provide the TFS SDK(API), if we want connect to TFS by custom code, the code must can invoke the TFS API, then we can connect to TFS and create work items using TFS API.

    So I want to confirm with you that how do you connect to VS SDK using your VBA code(without create additional files)? If you can connect to VS SDK using your VBA code, I think we can still connect to TFS API using your VBA code. If you not sure can connect to VS SDK or not, I suggest you contact Excel experts for the better response about can we connect to VS SDK using the VBA code(without create additional files).  


    John Qiao [MSFT]
    MSDN Community Support | Feedback to us

    Monday, February 20, 2012 1:53 AM
  • John,

    I don't understand your response. I have explained what the goal is and what I am trying to do. Your response doesn't make sense to me so I am not sure how to respond. I am asking if it is possible to connect to TFS from Excel using VBA and if so, for an example of it - see original posting.

    Thanks!

    Monday, February 20, 2012 12:46 PM


  • Hi Tim, 

    Thanks for your reply.

    Ok, to simple words, as far
    as I know, you must create the class library project and add TFS DLL references
    if you want connect to TFS using VBA.    



    John Qiao [MSFT]
    MSDN Community Support | Feedback to us


    Tuesday, February 21, 2012 3:07 AM