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