VBA calls Web Service but ends up on localhost

Have you tried to use a Web Service from Microsoft Excel? It shoud be fairly easy. And it certainly is once you go over some hurdles.

You simply google for "VBA web service" and get lots of links that tell you how to call web services from Excel or any VBA programs. I don't know why but all of them work only in simple environment of localhost but break when you try to point to your web service on another machine.

So, what all these articles recommend to do is the following:

• Create a macro and assign it to a button or other action and use VBA to write the Web Service Call

• Add a “Reference” to the VBA Function to the SOAP Type Library and to Microsoft XML, v3 or v4

• Connect to the Web Service using the following:

Private client As SoapClient
client.MSSoapInit http://localhost:7001/webservices/ws/test/HelloWorldContract.wsdl

• Call the methods on the web service:

hello = client.getHello()

Everything works OK until you move your web service on another machine. In this case WSDL is read fine but the call of web service's method gets timed out. And the SOAP sniffer shows no traffic from the client machine.

To save your time I will not put here entire debugging process. What I have found that even though the initialization is done using the WSDL on the remote machine, the web service call itself goes to the localhost. Here is the code that works when the web service is deployed not on localhost:

Public Sub test()
  Dim client As MSSOAPLib.SoapClient
  Set client = New SoapClient
  Dim hello As String

  Call client.mssoapinit("http://moscow:7001/webservices/ws/test/HelloWorldContract.wsdl", "HelloWorld", "HelloWorldSoap")
   client.ConnectorProperty("EndPointURL") = "http://moscow:7001/webservices/ws/test/HelloWorld.jws"


  hello = client.getHelloWorld()

End Sub

Now it works fine. The key line here is setting EndPointURL connector property of the client.

---

* Example web service is developed and deployed under Weblogic Workshop:

package ws.test;

import java.util.Date;

public class HelloWorld implements com.bea.jws.WebService
{
  static final long serialVersionUID = 1L;

  /**
   * @common:operation
   */
  public String getHelloWorld() {
    System.out.println("HelloWorld.getHelloWorld is called at " + new Date());
    return "Hello World!";
  }
}