最近需要用到Excel调用JAVA Webservice,一开始用VBA调用.Net写的Webservice,例如:
Dim objSoapClient As New SoapClient
objSoapClient.ClientProperty("ServerHTTPRequest") = True
objSoapClient.mssoapinit ("http://svr-015:60/MMS/CalMetrics.asmx?WSDL")
s = objSoapClient.HelloWorld()
出现错误:WSDLReader:XML Paser failed at linenumber1,lineposition 5,reason is:Invalid xml declaration。
解决方法:放弃用SoapClient,改用"MSXML2.XMLHTTP",具体参考如下代码:
'get方式调用JWS
Set objhttp = CreateObject("MSXML2.XMLHTTP")
Set xmlDOC = CreateObject("MSXML.DOMDocument")
strWebserviceURL = "http://se.hundsun.com/axis/services/JiraService?method=getProject"
'设置参数及值
objhttp.Open "get", strWebserviceURL, False
'设置Content-type,很重要
objhttp.setRequestHeader "Content-Type", "text/xml;charset=utf-8"
objhttp.send (strWebserviceURL)
bOK = xmlDOC.Load(objhttp.responseXML)
'判断状态
If objhttp.Status = 200 Then
' xmlstr = xmlDOC.XML
ConvDatasetToRecordsetProject xmlDOC '解析XML
End If
Public Function ConvDatasetToRecordsetProject(ByVal voNL As DOMDocument)
Err.Clear
On Error GoTo ErrHandle
Application.Cursor = xlWait
Dim iXMLValueNode As IXMLDOMNode
Dim iXMLNodeList As IXMLDOMNodeList
Set iXMLNodeList = voNL.selectNodes("soapenv:Envelope/soapenv:Body/multiRef")
Dim str As String
For Each iXMLValueNode In iXMLNodeList
str = iXMLValueNode.childNodes.Item(1).Text + "/" + iXMLValueNode.childNodes.Item(0).Text
proList.AddItem str
Next
Application.Cursor = xlDefault
ExtSub:
Exit Function
ErrHandle:
Application.Cursor = xlDefault
MsgBox Err.Description, vbCritical + vbOKOnly
GoTo ExtSub
End Function