12

As the question asked, how do I call WCF client from Excel 2003 VBA?

I saw there is a place where I can call web service, but I have searched through Google, all the results I get is not possible to call a WCF client from VBA.

I would like to know sort of what method to use before I do anything with my code, don't want to waste the time and discover later that it is not possible to do that.

abatishchev
  • 98,240
  • 88
  • 296
  • 433
PlayKid
  • 1,345
  • 5
  • 20
  • 33
  • 1
    What's the code look like to call a web service? It shouldn't be a lot different, but I don't know for sure. – Tad Donaghe Jun 18 '09 at 16:12
  • 1
    If Google doesn't tell you how to call a web service from Excel, then maybe the Excel documentation or online resources will? – John Saunders Jun 18 '09 at 16:50
  • 1
    what's the reason you want to use a WCF client? What does the remote service look like? Does it use security, transactions, queuing? It may be simpler to use MSXML from VBA, depending on the target service. – Cheeso Jun 18 '09 at 20:36
  • 1
    @wongtatseng: did you mean you want Excel to call a WCF Client, or a WCF Service? – John Saunders Jun 18 '09 at 21:54

4 Answers4

12

You might want to look at using the WCF Service Moniker which lets you invoke a WCF Service from VBA without installing anything on the Excel client machine other than the .NET Framework.

Dim addr As String
addr = "service:mexAddress=""net.tcp://localhost:7891/Test/WcfService1/Service1/Mex"","
addr = addr + "address=""net.tcp://localhost:7891/Test/WcfService1/Service1/"","
addr = addr + "contract=""IService1"", contractNamespace=""http://tempuri.org/"","
addr = addr + "binding=""NetTcpBinding_IService1"",bindingNamespace=""http://tempuri.org/"""

Dim service1 As Object
Set service1 = GetObject(addr)

MsgBox service1.GetData(12)

I've written out a complete step-by-step example.

/Damian

Damian
  • 4,723
  • 2
  • 32
  • 53
  • Damian, are you able to pass an array of primatives to your wcf servcie method? – priehl Aug 21 '13 at 17:00
  • I don't recall, but I was passing a variant array - maybe just put the primitive values in the there: return new object[] { 123, 456, 789 }; – Damian Aug 21 '13 at 17:58
2

I tried for quite a while to call a SOAP based wcf service from excel vba without any luck.

Instead I changed my service to REST binding (webHttpBinding). That way I could load the data stright into an xml map like it was any other xml file. Worked well for me, but I was only trying to import some data.

As for SOAP; an answer in this question mentions the Web Services Toolkit all the examples I could find suggested to use.

Community
  • 1
  • 1
Cwoo
  • 355
  • 4
  • 12
2

I wouldn't use VSTO in this case. COM Interop is probably the best way to go, so long as you don't want any security in your SOAP messages.

  1. Write WCF client, with necessary binding
  2. Expose assembly for COM Interop
  3. Reference exposed assembly in Excel VBA
John Saunders
  • 160,644
  • 26
  • 247
  • 397
1

I have done this with limited success using the Office 2003 Web Services Toolkit, but not without some pain.

Note that this is not supported by Microsoft, and has a number of restrictions which may make it unsuitable for production use. E.g. it does not support web services that return an empty collections : see this StackOverflow question.

If you want to go this route despite it being unsupported, download the toolkit and post any problems you're having here.

Microsoft recommends using VSTO, but this doesn't integrate easily with VBA...

Community
  • 1
  • 1
Joe
  • 122,218
  • 32
  • 205
  • 338