Update TFS WorkItem from Microsoft Access using VBA Code

Many IT users are not familiar with C# code or other languages to create small applications. Usually they build their applications using Microsoft Access/Excel since it does not require any programming experience. They can add forms/reports without writing a single line of code. In my organization, some departments like release management and IT testing have small applications developed in Microsoft Access or Microsoft Excel to track and manage their own work.
When the change management implements Team foundation Server and it uses TFS WORKITEMs as a repository for work like Tasks, Bugs, etc, and for sure they need to integrate between systems.
Since there are no official connector between MS Access and TFS workitem specialty MS office users uses VBA code to do a changes, It needs a lot of manual work to integrate the data between Access or Excel and TFS .
In this post will show you how to connect VBA code to TFS workitem API.
You can download the demo or source code from links below
Article_demo
Article_src
Below is the steps to create this DLL API

Create a new C# class library project: 

Add System.Runtime.InteropServices references to expose the class as COM to use this class from VBA.
Add these TFS DLL references to use them when managing WorkItems.

  • Microsoft.TeamFoundation.Client
  • Microsoft.TeamFoundation.WorkItemTracking.Client
When you open the calls, the code adds this:

[ComVisible(true),
ClassInterface(ClassInterfaceType.AutoDual)] //this command to expose the class
public class WorkItemTraking
{

}

Add the connection to TFS using the below method:

private void TFSconnect()
{
    try
    {
        System.Net.NetworkCredential _credential =
	(System.Net.NetworkCredential)System.Net.CredentialCache.DefaultCredentials;
        Uri _path = new Uri("http://TFSSERVER:8080/tfs/ColectionName");
        TfsTeamProjectCollection server = new TfsTeamProjectCollection(_path);

        server.Authenticate();
        store = server.GetService(typeof(WorkItemStore)) as WorkItemStore;

        if (false == server.HasAuthenticated)
        {
            throw new Exception("Could not authenticate while connecting to tfs");
        }
    }
    catch (Exception _exp)
    {
        throw;
    }
}

In this example, I will write two methods. First, read the workitem database on WIQ (workitem Query), the user will pass the query as string and the result is returned as XML.
So I will serialize the datatable to XML:

[ComVisible(true), Description("Get workitem")]
public string GetWorkItem(string WIQuery)
{
    TFSconnect(); //Connect to TFS collection
    DataTable _returnResult;
    try
    {
        WorkItemCollection _workitemcol;
        _workitemcol = store.Query(WIQuery); 	// pass the query and return the result in collection
        _returnResult = CreateDatatable(_workitemcol); // parse the query to return it as datatable
        //Serialize DataTable to XML
        StringWriter _streamData = new StringWriter();
        _returnResult.WriteXml(_streamData);

        return _streamData.ToString();
    }
    catch (Exception _exp)
    {
        throw new Exception(_exp.ToString());
    }
}

The second method is to change the workitem fields:

[ComVisible(true), Description("Set workitem")]
public Boolean SetWorkItem(int WIID, string WIField, string Value)
{
    TFSconnect();
    try
    {
        WorkItem _workitem = store.GetWorkItem(WIID); // read WorkItem ID
        _workitem.Fields[WIField].Value = Value;
        _workitem.Save();
        return true;
    }
    catch (Exception _exp)
    {
        throw new Exception(_exp.ToString());
    }
}

The next step is to create and sign an assembly with a strong name using the Assembly Linker. From command prompt, use this command to generate snk file:

sn -k sgKey.snk

From your application, right click on project and select signing tab and browse the generated file:

Save your application and make sure it builds successfully.

After we finish building the DLL application that accesses TFS, we need to connect from Access using VBA code, but first we should generate TLB file to reference it from VBA using this command to generate tlb:

C:\Program Files\Microsoft Visual Studio 9.0\VC> regasm [Source DLL] OutputDLL.tlb /codebase

Now open a new Access project and create a new form and open the Build event.
Select Tools-> References -> add TLB file.

Then, add this code to call the methods in the workitem class:

Public Sub CallTFS()

Dim objectServer As New WorkItemTraking.WorkItemTraking
Dim returnXML As String
Dim returnValue As Boolean
'this method changes the workitem filed value
returnValue = objectServer.SetWorkItem(112, "Impact", "Low")
MsgBox (returnValue)

'this method changes the workitem filed value
returnXML = objectServer.GetWorkItem("SELECT [System.Id], [System.Title] _
FROM WorkItems WHERE [System.Id] in (100, 200)
ORDER BY [System.Id]")

MsgBox (returnXML)

End Sub

Enjoy it 🙂

 

6 thoughts on “Update TFS WorkItem from Microsoft Access using VBA Code

Leave a Reply

Your email address will not be published. Required fields are marked *