CODEWITHSUNDEEP
excelvbaweb-services
EriqaBana
EriqaBana

Reputation: 125

How to consume a webservice in Excel 2013

I have a web service that I can call using Microsoft's WCFTestClient.exe. However, when I try to create a macro in Excel 2013 to call the web service, it doesn't work.

It doesn't error, but it doesn't actually run the method (RunScript) on the web server either.

Here is my code:

Private Sub web()


Dim sURL As String
Dim sEnv As String
Dim objHTTP As Object
Set objHTTP = CreateObject("MSXML2.XMLHTTP.6.0")
 sURL = "http://testserver:9001/TestService"

 On Error GoTo ErrorLabel


 sEnv = sEnv & "<s:Envelope xmlns:a=""http://www.w3.org/2005/08/addressing"" xmlns:s=""http://www.w3.org/2003/05/soap-envelope"" > "
 sEnv = sEnv & " <s:Header>"
 sEnv = sEnv & "  <a:Action s:mustUnderstand=""1"">http://tempuri.org/TestService/RunScript</a:Action>"
 sEnv = sEnv & "  <a:MessageID>urn:uuid:9405af92-1f5b-4ead-87</a:MessageID> <a:ReplyTo>"
 sEnv = sEnv & "        <a:Address>http://www.w3.org/2005/08/addressing/anonymous</a:Address>"
 sEnv = sEnv & "    </a:ReplyTo>"
 sEnv = sEnv & " </s:Header>"
 sEnv = sEnv & "  <s:Body>"
 sEnv = sEnv & "    <RunScript xmlns=""http://tempuri.org/""/> "
 sEnv = sEnv & "  </s:Body>"
 sEnv = sEnv & "</s:Envelope>"

 With objHTTP
    .Open "GET", sURL, False
    .setRequestHeader "Content-Type", "text/xml"
    .send sEnv
End With

 Set objHTTP = Nothing
ExitLabel:
   'Clean-up code, if any, goes here
   Exit Sub

ErrorLabel:
    'Error-handling code goes here
    Resume ExitLabel

End Sub

I know the webservice works, because I've created other clients that have successfully consumed the webservice. What am I doing wrong with this Excel 2013 macro?

**EDIT -- the above was using wsHTTPBinding, I switched it to use basicHTTPBinding:

<s:Envelope xmlns:s="http://schemas.xmlsoap.org/soap/envelope/">
  <s:Header>
    <Action s:mustUnderstand="1" xmlns="http://schemas.microsoft.com/ws/2005/05/addressing/none">http://tempuri.org/TestService/RunScript</Action>
  </s:Header>
  <s:Body>
    <RunScript xmlns="http://tempuri.org/" />
  </s:Body>
</s:Envelope>

I ran it in fiddler but eventually get a 504 - Gateway timout. Running the WCFClient and adding the service works as well as writing a small console app to consume it(by adding a web reference).

The soap xml I am using is the exact one from the WCFClient tool.

Thanks in advance!

Upvotes: 2

Views: 3942

Answers (2)

EriqaBana
EriqaBana

Reputation: 125

I finally got it to work by running the WCFClient and looking at the request in fiddler.

It appears that adding "SOAPAction" as a header :

SOAPAction: "http://tempuri.org/TestService/RunScript"

and changing the Request body to simply:

<s:Envelope xmlns:s="http://schemas.xmlsoap.org/soap/envelope/"><s:Body><RunScript xmlns="http://tempuri.org/"/></s:Body></s:Envelope>

as well as changing the verb to POST -- worked!

Upvotes: 1

Robin Mackenzie
Robin Mackenzie

Reputation: 19319

I think you missing a closing tag for <a:messageId>:

<s:Envelope xmlns:a="http://www.w3.org/2005/08/addressing" xmlns:s="http://www.w3.org/2003/05/soap-envelope" >  
  <s:Header>  
  <a:Action s:mustUnderstand="1">http://tempuri.org/TestService/RunScript</a:Action>  
  <a:MessageID>urn:uuid:9405af92-1f5b-4ead-87 
    <a:ReplyTo>        
      <a:Address>http://www.w3.org/2005/08/addressing/anonymous</a:Address>    
    </a:ReplyTo> 
    <!-- missing close tag for a:MessageID -->
  </s:Header>  
  <s:Body>    
  <RunScript xmlns="http://tempuri.org/"/>   
  </s:Body>
</s:Envelope>

Upvotes: 0

Related Questions