Using QueryTable to POST JSON to a RESTful API RRS feed

  • Question

  • Hi,

    I've created an Excel VBA clietn that does API calls to a REST service using MSXML2.XMLHTTP and i works fine on Windows.

    However - I now want my Excel app to work on Mac Office 2011 so I'm trying to use QueryTables instead of xmlhttp.

    Apparently in QueryTable - the .PostText is replaceing ["parameter"] with a parameter.

    Since my PostText is a JSON and it contains [] - Excel prompts the user for these values.

    Is there a way to escape the brackets ? ( tried \[ \] in vain).

    I also tried tricking Excel by replacing all the [....] with a single parameter ["brackets"] and providing the value for parameter "barckets" to contain the original backets text - it did not work - Excel still promplt the user.

    Any ideas ?

    Some code below.


    Public Function CallServiceXmlHttp(service As String, url_params As String, data As String) As Object
      ' send REST call to service and return JSON object
      ' url params is url parameter strint including the leading "?"
      Dim HttpReq As Object
      Set HttpReq = CreateObject("MSXML2.XMLHTTP")
      If Len(data) > 0 Then
        HttpReq.Open "POST", ApiUrl + service + url_params
        HttpReq.Open "GET", ApiUrl + service + url_params
      End If
      HttpReq.setRequestHeader "Authorization", AuthToken
      If Len(data) > 0 Then
        Dim d As Variant
        d = data
        'for some reason http send expects a Variant type
        Call HttpReq.Send(d)
        Call HttpReq.Send  ' support GET
      End If
      Do While HttpReq.readyState <> 4
      Dim resp As String

      resp = HttpReq.responseText
      If resp = "" Then
        Set CallService = Nothing
        Set CallService = jlib.parse(resp)
      End If
    End Function

    Tuesday, April 10, 2012 9:49 AM