Excel Vba 调用WebService的两种方式，解决MSSOAP30 64位不兼容问题

虽然是很老的技术了，但是碰到了，还是讲讲.....

Office Excel 采用Vba的方式调用WebService主要有两种方式：

1.n年前，微软提供MSSOAP30组件，为VC++编写的，这种方式和.Net调用WebService方式很类似，都是添加引用；但是致命的缺陷是不支持64位的Office程序（支持64位机器中运行32位的Office）；而且微软已经明确提醒开发者，已经放弃了MSSOAP30，替代为Framework的office开发。

此路不通....

2.采用基本的HTTP方式调用，这个方式非常好，理论上可以调用任何url，接受responseBody，完美支持WSDL、Restful的webservice，需要注意的是.Net开发的WSDL WebService需要开启Ajax调用方式

Function GetWsrrRlt(XmlStr As String) As String
Dim objHTTP, xmlDOC

'调用webservice方法
Set objHTTP = CreateObject("MSXML2.XMLHTTP")
Set xmlDOC = CreateObject("MSXML.DOMDocument")
strWebserviceURL = "http://192.168.0.114/wsrr.asmx/CallByXML"
objHTTP.Open "POST", strWebserviceURL, False'默认是POST方式
objHTTP.setRequestHeader "Content-Type", "application/json"
objHTTP.send ("{XmlInput:'" & XmlStr & "'}")'XmlInput是Ws的参数
GetWsrrRlt = CStr(BytesToBstr((objHTTP.responseBody), "utf-8"))
GetWsrrRlt = GetStringByJson(GetWsrrRlt)'.net3.5,.net4.0和以后的ws，默认返回的json字符格式为{"d":"这里是你期望的json，例如{"result":"1"}"}
'解析JSON格式
'Dim objSC, strJSON, objJS
'strJOSN = "{ ""myname"":""liucqa"",""myid"":""007"" }"
'Set objSC = CreateObject("MSScriptControl.ScriptControl")    '调用ScriptControl对象
'strJSON = "var o=" & strJOSN & ";"
'objSC.Language = "javascript"
'objSC.AddCode (strJSON)
'Set objJS = objSC.CodeObject.o
'MsgBox CallByName(objJS, "myname", VbGet) & "=" & CallByName(objJS, "myid", VbGet)


'Dim soapClient As SoapClient30
'Set soapClient = CreateObject("MSSOAP.SOAPCLIENT30")
'soapClient.MSSoapInit "http://192.168.0.114/wsrr.asmx?wsdl", "WSRR", "WSRRSoap", ""
'soapClient.ClientProperty("ServerHTTPRequest") = True
'GetWsrrRlt = CStr(soapClient.callbyxml(XmlStr))
End Function

 

转载于:https://www.cnblogs.com/qidian10/archive/2013/04/03/2997110.html