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