3

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 ??

ashleedawg
  • 20,365
  • 9
  • 72
  • 105
Dennys Lopes
  • 49
  • 1
  • 4
  • 1
    Not sure but mac web answers I have seen leverge libc.dylib / cURL. A POST example that may have worked: https://stackoverflow.com/a/34003425/6241235 . GET is easier and you have a variety of methods available e.g. http://www.agentjim.com/MVP/Excel/WebQueryHowTo.html – QHarr Oct 03 '18 at 15:13
  • Any updates? Did you get it to work? – Bob van Luijt Jul 10 '19 at 20:28

0 Answers0