Join 34,000+ subscribers and receive articles from our blog about software quality, testing, QA and security.
 

Accessing Testrail API using VBA in Excel


#1

Hi,

I am wanting to access the testrail api via vba in excel. I know this is possible but I can find no documentation other than your api .NET page which is seriously unhelpful (please don’t send me a link to this page assuming it will solve all my problems). While this provides some information for initial setup it is the most vague thing I have ever read in my life.

" Please add the contained project to your application or project in Visual Studio" okay but for vba where exactly is that location? is it the the whole folder or just the csproj file?

Once the binding is setup where do I go from there? How do I setup the json.NEt dependancy?

I’ve used the python api binding and it was super simple and straightforward in comparison.

Please help.

Thanks,
Sinclair


#2

Hi Sinclair,

Thanks for your posting. We don’t have experience with VBA but it should be possible to access the API from VBA without using the .NET API binding. TestRail’s API is a just generic JSON- and HTTP-based API and most programming languages/environments would support this. We have bindings for the most popular programming languages but you can also write your own integration. A starting point for VBA can be found here, for example:

https://discuss.gurock.com/t/how-get-images-to-get-results-using-api/3843/3
http://stackoverflow.com/questions/34616579/authentication-error-when-trying-to-connect-to-testrail-using-http-request

Cheers,
Tobias


#3

Thanks for the reply I managed to find a jira api video which translated across to using the testrail api. Thanks for the help.


#4

Hi @sinclair.mccutcheon

I’m looking for the same, you can help?

Thanks in advance.


#5

I used https://www.youtube.com/watch?v=nScy6S9tcmI&list=PLbu98QxRH81I6_CkO2nasj-Vlmpq2nWci and came up with this for VBA. You will need to go to Tools> References and make sure Microsoft XML, v6.0 is ticked in VBA editor. Ideally you will also have to parse the response data into json to use properly. The youtube link should have parse to json video with this in.

I came up with this for VBA to get response data.

Sub test()

Call testAuthen

End Sub

you can link the Sub to a macro button or run from VBA editor to call the code.

Private Function UserPassBase64() As String

    Dim objXML        As MSXML2.DOMDocument
    Dim objNode       As MSXML2.IXMLDOMElement
    Dim arrData()     As Byte
    
    Dim UserPass As String
    UserPass = "testrail username here:testrail password or api keyhere" 

The userpass needs to be in " " have a : inbetween. e.g. “joemcjoejoe@companyname.com:52812M23Tjt”

    arrData = StrConv(UserPass, vbFromUnicode)

    Set objXML = New MSXML2.DOMDocument
    Set objNode = objXML.createElement("b64")
    objNode.DataType = "bin.base64"
    objNode.nodeTypedValue = arrData

    UserPassBase64 = objNode.Text
   
End Function

Public Function testAuthen()
    
    Dim getResultsAsRun As New MSXML2.XMLHTTP60
    Dim url As String
        
    url = "Your testrail url here/index.php?/api/v2/get/postmethod here/run number here"

The url needs to be your companies url for testrail (e.g https://testrail.companyName.com /index.php?/api/v2/getrun/4112

    With getResultsAsRun
        .Open "Get", url, False
        .setRequestHeader "Content-Type", "application/json"
        .setRequestHeader "Authorization", "Basic " & UserPassBase64
        .send
        ActiveSheet.Range("A1").Value = .responseText 

This will put the data into Cell Range A1 of the active sheet. But can be manipulated however you like.

    If .Status = "401" Then
         MsgBox "Not authorized or invalid username/password"

If your userpass or url is incorrect this should generate an error message.

    End If
    End With

Hope that helps


#6

Thanks @sinclair.mccutcheon.

I’ll try


#7

Hi @sinclair.mccutcheon

I’d like to bring this topic up again, as I am also looking for a solution.
As we are doing all our reporting (test cases & defects) in excel, I used to export (as shown above) the testrail data and copy it to my target excel.
Same for defects from jira.
Now I came up with the idea to fetch data from jira automatically to excel. So everytime I open the excel file or hit the refresh data button, the excel collects the data from jira. This works Perfect!!
Here is how this works Atlassian Jira

Is there any chance to implement something like this for a Test Plan/Run? With some way to configure which fields should be fetched (similar to the filter configuration in jira).

This would be awesome.


#8

Hi, I’m trying to do the same thing here in Excel, yet I’m running into Authentication issues.

My code is pretty much the same thing:

Function testAuthen(strURL As String)

Dim xmlConnect As New MSXML2.XMLHTTP60

Debug.Print ("URL:  " & strURL)
Debug.Print ("Auth:  Basic " & userPassBase64)

xmlConnect.Open "GET", strURL, False
xmlConnect.setRequestHeader "Authorization", "Basic " & userPassBase64
xmlConnect.setRequestHeader "Content-Type", "application/json"
xmlConnect.send ("")

Debug.Print ("Output: " & xmlConnect.responseText)
Debug.Print ("Status: " & xmlConnect.Status)

End Function

Yet, when I run this I get an “Authentication failed” error. The curious thing is that I’ve done essentially the same thing in Python. I output the encoded Authentication string and URL and they come back as exactly the same as what I’m getting here. Yet with Python, I connect with no issue. What am I missing here on the Excel side?