Though MSDN suggests using stubs generated from the WSDL by MS Soap Toolkit for connecting to an HTTP Web Service from within VBA Excel, but it might not work as you would like it to, especially for a SOA-compliant web service and particularly in the cases where you need to access the service via a Proxy Server.
I have used the SOAP Connector called 'httpConnector30' successfully to connect to an HTTP Web Service without any issues. This connector is a part of the Microsoft SOAP Library named MSSOAPLib30 and you got to make sure that this library is referenced in your Excel installation. If it's not already there in your excel, just add the corresponding DLL and you're done.
Using httpConnector30 is different from consuming a Web Service by creating the stubs using MS Soap Toolkit. 'httpConnector30' requires you to specify the actual Web Service URL whereas the toolkit asks you the WSDL url and creates stubs accordingly, which you use in your VBA code. I personally think using 'httpConnector30' is easier and more straightforward if you have the service url.
Before we jump on to the code listed below, let's understand what all the code does broadly:-
Instantiating the SOAP Connector
Setting up the Proxy Server and Port (if access needed via Proxy)
Setting up the Web Service URL (not WSDL url)
Setting up Timeout period for the service call
Setting up the SOAP Action i.e., the actual method to be called
Beginning SOAP Message and getting connector's Input Stream
Building up the SOAP Request (as per your Web Service definition)
Sending the SOAP Message (this is where the service call is made)
Initializing the SOAP Reader and reading the SOAP Response
Note: in the below code I have not shown the exception handling blocks, which you should include to grab and handle the potential errors gracefully. For example: you should first check the 'connector' as 'Not Nothing' before trying to load the 'reader' with connector's output stream.
Additionally, I've assumed that the first node (except the generic envelope and body) of the SOAP Response is actually a List and hence I've put a loop to iterate through it. 'Set response = reader.RPCResult.childNodes' actually sets the 'response' to the first node of the SOAP Response as read from the reader (which itself is loaded with the connector's output stream).
Just to make your service consumption code robust and independent of the Response Structure changes (like addition of new nodes and/or reordering of nodes), in your client code, you should iterate through all the SOAP Response nodes and compare the current node name with your Service Response node names (you can get them in the service WSDL) and subsequently handle the particular node, say inside an if-block. This will make sure that your code doesn't fail abruptly in case Service Response Structure changes. For example: it will avoid any code failure say because you had written it assuming the first node in the response was a List and let's say the service response structure changes make it the second node in the response - maybe because the service provider needed to add another field in the response and also wished to make that the first field. I know the service provide will certainly let the client developers know about the changes, but if you make your code flexible to such possible changes, nothing like it... right?
Public Sub HTTPConnectivityTest()
'Instantiating the SOAP Connector
Dim connector As New MSSOAPLib30.HttpConnector30
'Setting up the Proxy Server and Port
connector.Property("ProxyServer") = "fully-qualified-proxy-server-or-IPAddress:Port"
'Setting up the Web Service URL
connector.Property("EndPointURL") = "http://web-service-server:port/webservices/SampleService.v1"
'Setting up Timeout period for the service call
connector.Property("Timeout") = 2000 '2 minutes
'Setting up the SOAP Action i.e., the actual method to be called
connector.Property("SoapAction") = "urn:getSampleData"
'Beginning SOAP Message
connector.BeginMessage
'Initializing SOAP Serializer with connector's input stream
Dim writer As New MSSOAPLib30.SoapSerializer30
writer.Init connector.InputStream
'Building the SOAP Request - envelope and body
writer.startEnvelope ' <SOAP-ENV:Envelope>
writer.startBody ' <SOAP-ENV:Body>
'Populating the SOAP Request with actual input parameters
writer.startElement "SampleServiceRequest", "service namespace", , "s3" ' <SampleServiceRequest>
writer.startElement "inputParam1" ' <inputParam1>
writer.writeString "param1 value" ' value of inputParam1
writer.endElement ' </inputParam1>
writer.startElement "inputParam2" ' <inputParam2>
writer.writeString "param2 value" ' value of inputParam2
writer.endElement ' </inputParam2>
writer.startElement "inputParam3" ' <inputParam3>
writer.writeString "param3 value" ' value of inputParam3
writer.endElement ' </inputParam3>
'Populating list-type parameter
writer.startElement "paramList" ' <paramList>
'Adding node #1 to the list-type param
writer.startElement "paramListNode" ' <paramListNode>
writer.startElement "nodeParam1" ' <nodeParam1>
writer.writeString "value1" ' value of nodeParam1
writer.endElement ' </nodeParam1>
writer.startElement "nodeParam2" ' <nodeParam2>
writer.writeString "value1" ' value of nodeParam2
writer.endElement ' </nodeParam2>
writer.endElement ' </paramListNode>
'Adding node #2 to the list-type param
writer.startElement "paramListNode" ' <paramListNode>
writer.startElement "nodeParam1" ' <nodeParam1>
writer.writeString "value2" ' value of nodeParam1
writer.endElement ' </nodeParam1>
writer.startElement "nodeParam2" ' <nodeParam2>
writer.writeString "value2" ' value of nodeParam2
writer.endElement ' </nodeParam2>
writer.endElement ' </paramListNode>
'Population of list-type param ends here
writer.endElement ' </paramList>
'Finishing the SOAP Request
writer.endElement ' </SampleServiceRequest>
writer.endBody ' </SOAP-ENV:Body>
writer.endEnvelope ' </SOAP-ENV:Envelope>
'Sending the SOAP Message (this is where the service call is made)
connector.EndMessage
'Defining SOAP Reader and initializing it with connector's output stream
Dim reader As New MSSOAPLib30.SoapReader30
reader.Load connector.OutputStream
'Parsing the SOAP Response
Dim response As MSXML2.IXMLDOMNodeList
'Setting the response to the first node of the SOAP Response
Set response = reader.RPCResult.childNodes
Dim node As MSXML2.IXMLDOMNode
'Iterating through the first node of SOAP Response knowing it is a list
For Each node In response
Dim nodeName As String
Dim nodeValue As String
nodeName = node.nodeName
nodeValue = node.nodeTypedValue
'Showing the Node Name and Value on Alert Boxes
MsgBox node.nodeName & ": " & node.nodeTypedValue
Next node
End Sub
I have used the SOAP Connector called 'httpConnector30' successfully to connect to an HTTP Web Service without any issues. This connector is a part of the Microsoft SOAP Library named MSSOAPLib30 and you got to make sure that this library is referenced in your Excel installation. If it's not already there in your excel, just add the corresponding DLL and you're done.
Using httpConnector30 is different from consuming a Web Service by creating the stubs using MS Soap Toolkit. 'httpConnector30' requires you to specify the actual Web Service URL whereas the toolkit asks you the WSDL url and creates stubs accordingly, which you use in your VBA code. I personally think using 'httpConnector30' is easier and more straightforward if you have the service url.
Before we jump on to the code listed below, let's understand what all the code does broadly:-
Instantiating the SOAP Connector
Setting up the Proxy Server and Port (if access needed via Proxy)
Setting up the Web Service URL (not WSDL url)
Setting up Timeout period for the service call
Setting up the SOAP Action i.e., the actual method to be called
Beginning SOAP Message and getting connector's Input Stream
Building up the SOAP Request (as per your Web Service definition)
Sending the SOAP Message (this is where the service call is made)
Initializing the SOAP Reader and reading the SOAP Response
Note: in the below code I have not shown the exception handling blocks, which you should include to grab and handle the potential errors gracefully. For example: you should first check the 'connector' as 'Not Nothing' before trying to load the 'reader' with connector's output stream.
Additionally, I've assumed that the first node (except the generic envelope and body) of the SOAP Response is actually a List and hence I've put a loop to iterate through it. 'Set response = reader.RPCResult.childNodes' actually sets the 'response' to the first node of the SOAP Response as read from the reader (which itself is loaded with the connector's output stream).
Just to make your service consumption code robust and independent of the Response Structure changes (like addition of new nodes and/or reordering of nodes), in your client code, you should iterate through all the SOAP Response nodes and compare the current node name with your Service Response node names (you can get them in the service WSDL) and subsequently handle the particular node, say inside an if-block. This will make sure that your code doesn't fail abruptly in case Service Response Structure changes. For example: it will avoid any code failure say because you had written it assuming the first node in the response was a List and let's say the service response structure changes make it the second node in the response - maybe because the service provider needed to add another field in the response and also wished to make that the first field. I know the service provide will certainly let the client developers know about the changes, but if you make your code flexible to such possible changes, nothing like it... right?
Public Sub HTTPConnectivityTest()
'Instantiating the SOAP Connector
Dim connector As New MSSOAPLib30.HttpConnector30
'Setting up the Proxy Server and Port
connector.Property("ProxyServer") = "fully-qualified-proxy-server-or-IPAddress:Port"
'Setting up the Web Service URL
connector.Property("EndPointURL") = "http://web-service-server:port/webservices/SampleService.v1"
'Setting up Timeout period for the service call
connector.Property("Timeout") = 2000 '2 minutes
'Setting up the SOAP Action i.e., the actual method to be called
connector.Property("SoapAction") = "urn:getSampleData"
'Beginning SOAP Message
connector.BeginMessage
'Initializing SOAP Serializer with connector's input stream
Dim writer As New MSSOAPLib30.SoapSerializer30
writer.Init connector.InputStream
'Building the SOAP Request - envelope and body
writer.startEnvelope ' <SOAP-ENV:Envelope>
writer.startBody ' <SOAP-ENV:Body>
'Populating the SOAP Request with actual input parameters
writer.startElement "SampleServiceRequest", "service namespace", , "s3" ' <SampleServiceRequest>
writer.startElement "inputParam1" ' <inputParam1>
writer.writeString "param1 value" ' value of inputParam1
writer.endElement ' </inputParam1>
writer.startElement "inputParam2" ' <inputParam2>
writer.writeString "param2 value" ' value of inputParam2
writer.endElement ' </inputParam2>
writer.startElement "inputParam3" ' <inputParam3>
writer.writeString "param3 value" ' value of inputParam3
writer.endElement ' </inputParam3>
'Populating list-type parameter
writer.startElement "paramList" ' <paramList>
'Adding node #1 to the list-type param
writer.startElement "paramListNode" ' <paramListNode>
writer.startElement "nodeParam1" ' <nodeParam1>
writer.writeString "value1" ' value of nodeParam1
writer.endElement ' </nodeParam1>
writer.startElement "nodeParam2" ' <nodeParam2>
writer.writeString "value1" ' value of nodeParam2
writer.endElement ' </nodeParam2>
writer.endElement ' </paramListNode>
'Adding node #2 to the list-type param
writer.startElement "paramListNode" ' <paramListNode>
writer.startElement "nodeParam1" ' <nodeParam1>
writer.writeString "value2" ' value of nodeParam1
writer.endElement ' </nodeParam1>
writer.startElement "nodeParam2" ' <nodeParam2>
writer.writeString "value2" ' value of nodeParam2
writer.endElement ' </nodeParam2>
writer.endElement ' </paramListNode>
'Population of list-type param ends here
writer.endElement ' </paramList>
'Finishing the SOAP Request
writer.endElement ' </SampleServiceRequest>
writer.endBody ' </SOAP-ENV:Body>
writer.endEnvelope ' </SOAP-ENV:Envelope>
'Sending the SOAP Message (this is where the service call is made)
connector.EndMessage
'Defining SOAP Reader and initializing it with connector's output stream
Dim reader As New MSSOAPLib30.SoapReader30
reader.Load connector.OutputStream
'Parsing the SOAP Response
Dim response As MSXML2.IXMLDOMNodeList
'Setting the response to the first node of the SOAP Response
Set response = reader.RPCResult.childNodes
Dim node As MSXML2.IXMLDOMNode
'Iterating through the first node of SOAP Response knowing it is a list
For Each node In response
Dim nodeName As String
Dim nodeValue As String
nodeName = node.nodeName
nodeValue = node.nodeTypedValue
'Showing the Node Name and Value on Alert Boxes
MsgBox node.nodeName & ": " & node.nodeTypedValue
Next node
End Sub
