Update TFS WorkItem from Microsoft Access using VBA Code

Ayman Zaza

4.50/5 (2 votes)

Apr 18, 2011

CPOL

2 min read

36234

812

This document is to access TFS from VBA code, since we can't reference Team Foundation Server DLLs from VBS directly.

Introduction

Many IT users are not familiar with C# code or other languages to create small applications. They build their applications using Microsoft Access/Excel because it does not need 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 their own work, but when the change management implements Team foundation Server and it uses TFS workitem as a repository for work like Task, Bug, etc. workitem, in most cases, it needs a lot of manual work to integrate the data between Access or Excel and TFS and there is no integration between these applications.

Step 1

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://devprosrv07:8080/tfs/CoreES");
        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.

Step 2

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 (47492, 47512)
ORDER BY [System.Id]")

MsgBox (returnXML)

End Sub

History

18^th April, 2011: Initial post