Is it possible to access VSTS from Excel VBA macro? RRS feed

  • Question

  • Hi,

    One small help please. I would like to know if it is possible to connect to VSTS through Excel VBA macro using REST API so as to extract data from VSTS into excel sheet or to upload data into VSTS from excel sheet.

    For example, I wrote the below VBA code so as to connect to my VSTS account and then extract the details of all the bugs that I had created inside my VSTS account, but when I run the code, it always gives me either "Operation Timed Out" error or "Non-Authoritative Information" status.

    Option Explicit

    Sub ExtractVstsBugs()

    Dim objHttp As MSXML2.ServerXMLHTTP

    Dim strUrl As String, strUserName As String, strPassword As String, strPersonalAccessToken As String

    Dim byteArr() As Byte

    Set objHttp = New MSXML2.ServerXMLHTTP

    strUrl = "https://<MyAccountName><ProjectName>/_apis/wit/workitems/$Bug?api-version=1.0"

    strUserName = "<MyAccountLoginName>"

    strPassword = "<MyAccountPassword>"

    strPersonalAccessToken = "<MyAccountPersonalAccessToken>"

    objHttp.Open "GET", strUrl, False

    byteArr = strConv(strUserName & ":" & strPersonalAccessToken, vbFromUnicode)

    objHttp.SetRequestHeader "Authorization", "Basic " & encodeBase64(byteArr)


    End Sub

    In the above code, "encodeBase64" is a user defined function that I copied from Internet for converting an array of bytes to the corresponding base 64 encoded string. The definition of this function is as typed below. 

    Private Function encodeBase64(ByRef arrData() as Byte) as String

    Dim objXml As MSXML2.DOMDocument

    Dim objNode As MSXML2.IXMLDOMElement

    Set objXml = New MSXML2.DOMDocument

    Set objNode = objXml.createElement("b64")

    objNode.DataType = "bin.base64"

    objNode.nodeTypedValue = arrData

    encodeBase64 = objNode.Text

    Set objNode = Nothing

    Set objXml = Nothing

    End Function

    When I try to run the above VBA code from my office, I am getting the error as "Operation Timed Out" in the statement "objHttp.Send". When I try to run the code from my home, I am getting the objHttp.statusText value as "Non-Authoritative Information" after the statement "objHttp.Send" is executed.

    Please let me know what is wrong with the above VBA code and how the above code should be modified so as to run successfully. Also please let me know if it is possible at all to connect to and extract information from VSTS using Excel VBA macro.

    Thanks in advance.

    Thursday, December 21, 2017 12:35 PM

All replies

  • Hello,

    This forum (Excel for Developers) is for development issues about using Excel Object Model. Your issue about using MSXML2 is out of scope. I would move this thread into VBA forum. Thanks for your understanding.



    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact

    Friday, December 22, 2017 2:57 AM