Java : Connecting to an HTTP Web Service from VBA Excel via a Proxy Server

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