Web Services in VBA using MSXML.ServerXMLHTTP on Mac

3.2k views Asked by At

I have an Add-In for Excel that, at one point, I need to send and receive data via Web Services using the Microsoft XML DLLs.

The problem is that now I need to make this Add-in available for Mac.

Right from the outset I came across a problem, MSXML2.XMLHTTP is not available for Mac.

Public Function PostWebservice(ByVal AsmxUrl As String, ByVal SoapActionUrl _
        As String, ByVal XmlBody As String, sPOST As String) As String

    Dim objDom As Object, objXmlHttp As Object, strRet As String
    Dim intPos1 As Integer, intPos2 As Integer
    Set objDom = CreateObject("MSXML2.DOMDocument") 'Create DOMDocument/XMLHTTP
    Set objXmlHttp = CreateObject("MSXML2.XMLHTTP")
    objDom.async = False 'Load XML
    objDom.LoadXML XmlBody

    If (sPOST = "") Then
        objXmlHttp.Open "GET", AsmxUrl, False 'Open the webservice
        objXmlHttp.setRequestHeader "Content-Type", _
            "text/xml; charset=utf-8" 'Create headings
        objXmlHttp.setRequestHeader "SOAPAction", SoapActionUrl
        objXmlHttp.setRequestHeader "Accept", "application/vnd.hmrc.1.0+json"
        objXmlHttp.send objDom.XML 'Send XML command
    Else
        objXmlHttp.Open "POST", AsmxUrl, False
        objXmlHttp.setRequestHeader "Content-Type", _
            "application/x-www-form-urlencoded"
        objXmlHttp.send sPOST
    End If

    strRet = objXmlHttp.ResponseText 'Get all response text from webservice
    Set objXmlHttp = Nothing 'Close object
    intPos1 = InStr(strRet, "Result>") + 7 'Extract result
    intPos2 = InStr(strRet, "<!--")

    If intPos1 > 7 And intPos2 > 0 Then
        strRet = Mid(strRet, intPos1, intPos2 - intPos1)
    End If
    PostWebservice = strRet 'Return result
    Debug.Print strRet
    Exit Function

Err_PW:
    PostWebservice = "Error: " & Err.Number & " - " & Err.Description
End Function

Does anyone know of any way to send and receive data via WebService (GET and POST) using VBA but that is compatible with Mac ??

0

There are 0 answers