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.
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. “firstname.lastname@example.org: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
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
.Open "Get", url, False
.setRequestHeader "Content-Type", "application/json"
.setRequestHeader "Authorization", "Basic " & UserPassBase64
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.
Hope that helps