This website uses cookies. By using our website, you agree to the use of cookies.

banner blog



TestBench Hacks: How do I become a master of the Excel chaos?

Welcome to the first contribution in the ‘TestBench Hacks’ category. This is where we want to give you practical tips (in no particular order) on how to get even more from the TestBench Cloud Services using simple methods.

Let's start with a topic that seems to affect many in the testing community.

Imagine you're in a large room full of people. Everyone there is professionally involved in software testing. A question is then thrown into the room: What tools do you use to manage your tests? There are a variety of tools to choose from, such as Bugtracker, testing or project management tools, MS Excel and Word. What do you think – how many people say MS Excel? If we’re assuming that the people in this room are also those who participated in the ‘State of Testing Report 2018’ then it's 59%. More than half also work with MS Excel; only under 50% with a test management tool.

The use of MS Excel is definitely a step in the right direction since the test planning or implementation can be managed and documented. But unfortunately, as we all know, the devil is in the detail: different versions of an Excel file quickly circulate because something has been duplicated or saved under the wrong file name. Reporting or properly tracking tests is therefore only possible under difficult conditions and with immense time and effort.

So far, so good. But what’s the solution to the problem? To introduce a test management tool? Our answer: Yes! Your initial reaction may well be to object because implementing such a system and transferring all of the data from your Excel spreadsheets to the tool would be extremely time-consuming. Our answer to that is: It doesn’t have to be! Because this is where TestBench Cloud Services and its REST API come into play.

Excel can be programmed. And even if we as developers consider the capabilities of VBA to be rather low, it is sufficient for connecting from Excel to TestBench CS using its REST API.

We’ll show you an example of what this sort of connection might look like. And because we know that even very old versions of Excel are still being used, we’ll take Excel 2010 as the basis.

The following subjects are covered in a series of articles on the TestBench blog:

• How do I connect to a REST API via VBA?
• How do I establish a connection to TestBench CS?
• What structure can I use for documenting my tests?
• How do I create a product?
• How do I create a structure consisting of epics, user stories and tests?
• Does that also work with a simpler structure?
• Can I change a test and document the change?
• How do I document the test execution?
• What data can TestBench CS return to me and how can I display it in Excel?

What to know first

A small warning. We only describe here how the connection from the 2010 version of Excel can be programmed on Windows 10. Exotic things such as Excel on a Mac or Android system are not part of our description.


Section 1: Can VBA actually REST? And if not, what do I have to do to make it so it can?

Unfortunately, in general, you cannot activate VBA REST interfaces directly. So let’s start by searching the big wide web and find:

WebHelpers v4.1.5
(c) Tim Hall -

This gives us methods that you can use to retrieve REST calls directly from VBA and process the responses.

We have adopted these class libraries for our project as they were provided.

In essence, we have the following classes:

• IWebAuthenticator v4.1.5 as an interface for authentication
• WebClient v4.1.5 for implementing requirements and which is also responsible for processing responses
• WebRequest v4.1.5 as an aid for the requirements
• WebResponse v4.1.5 as an aid for the responses of the Rest calls.

In addition, we use the following modules which are also from Tim Hall:

• WebHelpers v4.1.5

In order to make VBA REST-capable, all we have to do is incorporate these classes and modules into our project.


Section 2: Connect to the TestBench server

We now make the following assumption for this and the following sections. We determine that the data needed to connect to the TestBench CS Server is stored in a configuration file in the same directory in which the Excel is located. This file is always given the name "import_settings.ini". In addition, we decide that the structure of the file should look as follows:



Of course, these data are examples of the real data which should be used. And yes, from a security perspective, it’s not a great approach to store access data directly in plain text in such files. Taking a different approach here, we're intentionally making it your responsibility.

By the end of this section, we want to have achieved the following sequence:

1. read the configuration file;
2. connect to the data from the configuration file using the TestBench Server;
3. use the active login to read all the users created. This requires whichever user logged in to have tenant admin rights;
4. log out of the Testbench server.

To do this, we first need a piece of code to import the configuration file. We do this by creating a new module in Excel and giving it the name, mIniFile. In this module we need to privately declare a kernel function in order to be able to read defined sections of the file:

Private Declare Function GetPrivateProfileString Lib "kernel32" Alias "GetPrivateProfileStringA" (ByVal lpApplicationName As String, ByVal lpKeyName As Any, ByVal lpDefault As String, ByVal lpReturnedString As String, ByVal nSize As Long, ByVal lpFileName As String) As Long

We then need a private function in order to set the name and path of where the configuration file can be found:

Private Function IniFileName() As String

IniFileName = ThisWorkbook.Path & "\import_settings.ini"

End Function

And we would like a function that enables us to read a specific key in a defined section from the string returned from the file.

Public Function ReadIniFileString(ByVal Sect As String, ByVal Keyname As String) As String

Dim NumChar As Long
Dim RetStr As String * 256
Dim StrSize As Long
Dim sValue As String

sValue = ""
If Sect = "" Or Keyname = "" Then
MsgBox "Section or key to read not specified.", vbExclamation, "INI"
RetStr = Space(256)
StrSize = Len(RetStr)
NumChar = GetPrivateProfileString(Sect, Keyname, "", RetStr, StrSize, IniFileName())
If NumChar > 0 Then
sValue = Left$(RetStr, NumChar)
End If
End If

ReadIniFileString = sValue

End Function

With the module created, we can now obtain a specific key from a defined section of the configuration file at any time. Yes, we know we're being wasteful here with the File I/O Ressourcen because we reopen the file each time we access another key. But hey: you also need to do something to optimise our approach!

We’re now almost ready to establish the connection to the TestBench CS for the first time. There's just a small piece of code still missing.

You should know that the TestBench CS assigns a session token whenever a user logs in. This session token is valid for as long as the user is logged in and remains active. The session token must therefore be included in any of the user's actions. If the token is not included or is incorrect, the user's action will not be performed.

So first of all we have to log in, receive a session token and ensure that the session token is always used.

We build a new module again, which we call mImport.

We add the following value in the declaration part:

Option Explicit

' Log levels:
' L1Debug - detailed output including condensed Request/Response data
' L2Info - one line of information for successful operations
' L3Warn - the data row was processed and imported, but some data is invalid and wasn't imported
' L4Error - fatal error that stops the import
' This is the minimum level, e.g. L2Info will include L2..L4 in the logfile
Private Const LogLevel As Long = L2Info

' Write detailed debug output of Request/Response to Immediate Window? (sets WebHelpers.EnableLogging)
Private Const DoDebug As Boolean = False

' Logfile class object
Public oLog As clsLog

' Set in InitAndLogin()
Private TbClient As WebClient
Private TenantID As Long
Private UserID as Long
In diesem Modul definieren wir zunächst einmal eine sehr einfache Public Sub MainLoginLogoutTest.

Public Sub MainLoginLogoutTest()

If Not InitAndLogin() Then Exit Sub

Call Logout

End Sub


Logically, we also need to form the appropriate subroutines accessed here.

InitAndLogin is a private function that does the following in this version:

• activates interactive logging depending on a debug flag,
• creates the WebClient, which is then internally always used,
• obtains the URL and builds the base URL together,
• logs the user in,
• obtains the token from the response to the API call and links it in such a way that it is used with any other request.


Private Function InitAndLogin() As Boolean

Dim SessionToken As String
Dim dict As Scripting.Dictionary
Dim Url As String

' Log all API calls and responses to Immediate Window (Ctrl+g) ?
WebHelpers.EnableLogging = DoDebug

Set oLog = New clsLog
oLog.Init LogLevel

' Create a WebClient for executing requests
' and set a base url that all requests will be appended to
Set TbClient = New WebClient
Url = ReadIniFileString("Server", "URL")
' append (/)api/
If Right(Url, 1) <> "/" Then Url = Url & "/"
Url = Url & "api/"
TbClient.BaseUrl = Url

' Execute WebRequest for login

' Add parameters to the request JSON
Set dict = New Scripting.Dictionary
' Force login, logout other sessions of this user
dict.Add "force", True
' Login data is read from "import_settings.ini", see module mIniFile
dict.Add "tenantName", ReadIniFileString("User", "Tenant")
dict.Add "login", ReadIniFileString("User", "Login")
dict.Add "password", ReadIniFileString("User", "Password")

Dim Response As WebResponse
' Execute the request and work with the response
Set Response = ApiCall(HttpPost, "tenants/login/session", dict)

If Response.StatusCode = WebStatusCode.Created Then
' Parse json data to retrieve several variables
SessionToken = Response.Data("sessionToken")
TenantID = Response.Data("tenantId")
UserID = Response.Data("userId")

oLog.Add L2Info, "Login OK, UserID = " & UserID & ", TenantID = " & TenantID ' & ", SessionToken = " & SessionToken

' Set up automatic authentication - from now on, every executed request will have:
' Request.SetHeader "Authorization", "Bearer " & SessionToken
Dim iTbAuth As iTbCsAuthenticator
Set iTbAuth = New iTbCsAuthenticator

iTbAuth.Setup SessionToken
Set TbClient.Authenticator = iTbAuth

' Finished - success
InitAndLogin = True
oLog.Add L4Error, "Login", Response.Content
InitAndLogin = False
End If

End Function


An important aspect for all other calls is, at this point, that the TenantID and the UserID, which are used with the login, are always used again in other API calls.

So that we don’t need to carry out checks every time to find out whether the API calls are correct, we also build a small ‘support-function API call.’ Its parameters are the method used, the URL string and a dictionary filled with values.


Public Function ApiCall(Method As WebMethod, sResource As String, Optional dictBody As Dictionary = Nothing) As WebResponse

Dim Request As New WebRequest

' Set request properties from function parameters
Request.Resource = sResource
Request.Method = Method
Request.Format = Json

' Set path values from global values
If InStr(sResource, "{tenantId}") > 0 Then Request.AddUrlSegment "tenantId", TenantID

' Set request body JSON if a dictionary was passed
If Not dictBody Is Nothing Then
Set Request.Body = dictBody
End If

' Note: Instead of setting up iTbCsAuthenticator in InitAndLogin(), we could also do here:
' Request.SetHeader "Authorization", "Bearer " & SessionToken

' Log a condensed version of the request
oLog.Add L1Debug, "--> " & MethodToName(Request.Method) & " " & TbClient.GetFullUrl(Request), IIf(IsEmpty(Request.Body), "", CStr(Request.Body))

' Execute the request and return the response
Dim Response As WebResponse
Set Response = TbClient.Execute(Request)

' Log a condensed version of the response
oLog.Add L1Debug, "<-- " & Response.StatusCode & " " & Response.StatusDescription & " " & Response.Content

' ... and return it
Set ApiCall = Response

End Function


Logout is also a private sub, which is only used to destroy the user's session on the server and to enable the user to log off the system. This sub should always be used last. In principle, the TestBench CS is forgiving if a user logs on several times in succession, but in the end we do want to leave behind a clean system.

Logout is where we also see for the first time a principle that we will find again and again in the future.
In the URL for an API call, there are flexible parameters. These parameters are always specified with {parameter}. The API call function then internally replaces this parameter with the correct data needed.

Private Sub Logout()

' Don't pollute debug window with empty logout info
WebHelpers.EnableLogging = False

With ApiCall(HttpDelete, "tenants/{tenantId}/login/session", Nothing)
If .StatusCode = WebStatusCode.NoContent Then
oLog.Add L2Info, "Logout OK" & vbCrLf
oLog.Add L4Error, "Logout", .Content
End If
End With

End Sub


Now missing is only a button somewhere in the worksheet, which is simply the Sub MainLoginLogoutTest. We have thus, hopefully successfully, connected to the TestBench CS and then logged off again.

Incidentally, it’s relatively easily to check whether everything has worked out. In the directory where this Excel sheet is located, there is then also a log file, ConnectExcel2Testbench_BLOG.log.


Thomas Schulte

Thomas Schulte is Head of Development of TestBench Cloud Services. Prior to that, he worked in various areas of IT consulting and development. In several roles he has covered all parts of Software Life Cycle Management - from planning to development to support.