Call WCF service hosted on Azure from Excel through VBA

Question

Hi,

We are trying to call WCF service hostest on azure from excel using following VBA code

Function Check1()
Dim addr As String
addr = "service:mexAddress=""http://Service.cloudapp.net/AName.svc/mex"","
addr = addr + "address=""http://Service.cloudapp.net/AName"","
addr = addr + "contract=""IAName"", contractNamespace=""http://tempuri.org/"","
addr = addr + "binding=""BasicHttpBinding_IAName"", bindingNamespace=""http://tempuri.org/"""

Dim service1 As Object
Set service1 = GetObject(addr)

End Function

We are getting error : Run time Error '-2147221020 (800401e4). Automation error, Invalid Syntax

however trying with same code and using simple WCF service web role hosted on azure works fine.

Even with local simple WCF service it works fine

The service we are trying to call is consumed in Silverlight app. and same we want to use in excel

Configuration of service are same i.e basic http and mex endopint have been added

Please let us know what can be the issue

Answer 1

What is the difference between the "WCF service hosted on Azure" (the one that fails) and the "simple WCF service web role" (the one that works)?

You say you use the same code but I don't understand the difference between the two?

Was it a simplified test service that worked or the same service in a different setup?

Since your using BasicHttpBinding Fiddler probably can give you a lot of insight in the network traffic that both silverlight and excel cause in your tests, you might be able to compare and find the source of your issues.

Off topic:Using REST API's (like the MVC Web Api), in my experience, can make scenario's like these a lot less tedious, you can end up with VBA code like:

Dim zipResult As New MSXML2.DOMDocument
Dim zipService As New MSXML2.XMLHTTP
zipService.Open "GET", "http://webservicex.net/uszip.asmx/GetInfoByZIP?USZip=" + zip, False
zipService.send
zipResult.LoadXml (zipService.responseText)
Range("B3").Value = zipResult.selectSingleNode("//CITY").Text

Regards,

Valery

---

The sky has no limits.

Answer 2

Hi,

I intended to say simple meand there are no datacontracts which returns list , only string, int value return

Now the Observation is , if WCF service has simple operation contract . and we host this in cloud we are able create object of service like "Set service1 = GetObject(addr)" in VBA code

WSDL for which VBA code works Set service1 = GetObject(addr)"

- <wsdl:types>
- <xsd:schema targetNamespace="http://tempuri.org/Imports">
  <xsd:import schemaLocation="http://localhost:62611/NAME.svc?xsd=xsd0" namespace="http://tempuri.org/" />
  <xsd:import schemaLocation="http://localhost:62611/NAME.svc?xsd=xsd1" namespace="http://schemas.microsoft.com/2003/10/Serialization/" />
  </xsd:schema>
  </wsdl:types>

WSDL for which VBA code doesnot work Set service1 = GetObject(addr)" - Gives Runtime automation error

- <wsdl:types>
- <xsd:schema targetNamespace="http://tempuri.org/Imports">
  <xsd:import schemaLocation="http://localhost:62611/NAME.svc?xsd=xsd0" namespace="http://tempuri.org/" />
  <xsd:import schemaLocation="http://localhost:62611/NAME.svc?xsd=xsd1" namespace="http://schemas.microsoft.com/2003/10/Serialization/" />
  <xsd:import schemaLocation="http://localhost:62611/NAME.svc?xsd=xsd2" namespace="http://schemas.datacontract.org/2004/07/NAME.Storage" />
  <xsd:import schemaLocation="http://localhost:62611/NAME.svc?xsd=xsd3" namespace="http://schemas.datacontract.org/2004/07/System.Data.Objects.DataClasses" />
  <xsd:import schemaLocation="http://localhost:62611/NAME.svc?xsd=xsd4" namespace="http://schemas.datacontract.org/2004/07/System.Data" />
  </xsd:schema>

It i am using EDMX classses in the Data contract , i am not able to create object of the service in VBA code

EdmX designer has decoration for tables as following  

[XmlIgnoreAttribute()]
[SoapIgnoreAttribute()]

Answer 3

It is recommended to use .NET (VSTO). If you have the same problem with VSTO clients, then the problem may be on the service side, so feel free to continue the discussion. But if the problem only happens with VBA, you can consult a VBA forum: http://social.msdn.microsoft.com/Forums/en-US/isvvba/threads.