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