Solved: Add a WebService reference to an Excel 2010 sheet

[Paleo]

How can I add a WebService reference to an Excel 2010 sheet?

I know in Excel 2003 I should only go to: Microsoft Visual Basic Editor click: Tools > Web Service Reference…

Ok, I know I can go to VBE and create a class module and a regular module and do it all by using SOAP, but as it was a lot easier on Excel 2003 by simply using "Web Services References" I ask if there is still a way of using it in Excel 2010.

Any clues?

[Jan Karel Pieterse]

It must have been something you installed on that system. I don't have that reference available in my 2003.

[Paleo]

Yes Jan, thats true, sorry.

For Office XP you have Microsoft Office XP Web Services Toolkit, then for Office 2003 you have Office 2003 Web Services Toolkit, which give you that option, but I couldnt find a toolkit for Office 2010.

I know MS recomends you not to use SOAP Toolkit anylonger since Office 2007, but they also recomend you to abandonm VBA, what I wont do. Here you can see the worst recomendation ever: http://blogs.msdn.com/b/mab/archive/...mpossible.aspx
I say worst because you would endup by having to install .Net Framework, plus VSTO tools and on many clients this is simply not an option.

It might be solved by using this (http://msdn.microsoft.com/en-us/library/ms734757.aspx), but I simply cannot believe MS abandoned a simple way to go for a very complex way of doing a simple thing, thats why I am asking if someone knows an easier way.

[Jan Karel Pieterse]

I suspect you can use the 2003 version on 2010 too. NB: I found using the toolkit often to be overkill and just wrote a fairly simple VBA routine to "talk" to a webservice. It does mean you have to do the XML plumbing yourself however. The code below gives an idea (but returns an error XML because I got the arguments in the envelope wrong):

[vba]Option Explicit
Sub DoIt()
Dim sURL As String
Dim sEnv As String
Dim xmlhtp As New MSXML2.XMLHTTP40
Dim xmlDoc As New DOMDocument
sURL = "http://webservices.gama-system.com/e...ntConvertToEUR"

sEnv = "<?xml version=""1.0"" encoding=""utf-8""?>"
sEnv = sEnv & "<soap:Envelope xmlns:xsi=""http://www.w3.org/2001/XMLSchema-instance"" xmlns:xsd=""http://www.w3.org/2001/XMLSchema"" xmlns:soap=""http://schemas.xmlsoap.org/soap/envelope/"">"
sEnv = sEnv & " <soap:Body>"
sEnv = sEnv & " <CurrentConvertToEUR xmlns=""http://www.gama-system.com/webservices"">"
sEnv = sEnv & " <dcmEUR>100</dcmEUR>"
sEnv = sEnv & " <strBank>ING</strBank>"
sEnv = sEnv & " <strCurrency>USD</strCurrency>"
sEnv = sEnv & " <intRank>1</intRank>"
sEnv = sEnv & " </CurrentConvertToEUR>"
sEnv = sEnv & " </soap:Body>"
sEnv = sEnv & "</soap:Envelope>"

With xmlhtp
.Open "post", sURL, False
.setRequestHeader "Host", "webservices.gama-system.com"
.setRequestHeader "Content-Type", "text/xml; charset=utf-8"
.setRequestHeader "soapAction", "http://www.gama-system.com/webservic...ntConvertToEUR"
.setRequestHeader "Accept-encoding", "zip"
.send sEnv
xmlDoc.loadXML .responseText
MsgBox .responseText
End With
'xmlDoc.Save ThisWorkbook.Path & "\WebQueryResult.xml"
End Sub
[/vba]

[Paleo]

I will give it a try and let you know if it helps, but I believe I will have to end up by using a class. If so I will post the solution here too.

[Jan Karel Pieterse]

Sure. I guess this is a matter of preference, also depending on how versatile your program needs to be.

[Paleo]

No solution till now. You had a small error on the envelope. This is the correct envelope:
[VBA]
Option Explicit
Sub DoIt()
Dim sURL As String
Dim sEnv As String
Dim xmlHtp As New MSXML2.XMLHTTP40
Dim xmlDoc As New DOMDocument
sURL = "http://webservices.gama-system.com/e...ntConvertToEUR"

sEnv = "<?xml version=""1.0"" encoding=""utf-8""?>"
sEnv = sEnv & "<soap:Envelope xmlns:xsi=""http://www.w3.org/2001/XMLSchema-instance"" xmlns:xsd=""http://www.w3.org/2001/XMLSchema"" xmlns:soap=""http://schemas.xmlsoap.org/soap/envelope/"">"
sEnv = sEnv & " <soap:Body>"
sEnv = sEnv & " <CurrentConvertToEUR xmlns=""http://www.gama-system.com/webservices"">"
sEnv = sEnv & " <dcmValue>100</dcmValue>"
sEnv = sEnv & " <strBank>ING</strBank>"
sEnv = sEnv & " <strCurrency>USD</strCurrency>"
sEnv = sEnv & " <intRank>1</intRank>"
sEnv = sEnv & " </CurrentConvertToEUR>"
sEnv = sEnv & " </soap:Body>"
sEnv = sEnv & "</soap:Envelope>"

With xmlHtp
.Open "post", sURL, False
.setRequestHeader "Host", "webservices.gama-system.com"
.setRequestHeader "Content-Type", "text/xml; charset=utf-8"
.setRequestHeader "soapAction", "http://www.gama-system.com/webservic...ntConvertToEUR"
.send sEnv
xmlDoc.LoadXML .responseText
MsgBox .responseText
End With
'xmlDoc.Save ThisWorkbook.Path & "\WebQueryResult.xml"
End Sub
[/VBA]

Any way now I get an error telling ActiveX component cannot create the object.
I must be doing something stupid...

[Jan Karel Pieterse]

Did you add a reference to the Microsoft XML v6 library?

[Paleo]

Yes I did, must be something else.

Using Visual Studio 2010 I could make it work, but I dont want to force my users to install .NET framework in order to run my spreadsheet.

[Kenneth Hobs]

My code varies from that a bit. http://www.vbaexpress.com/forum/showthread.php?t=34354

[Paleo]

Thanks Kenneth but even with your example it keeps on giving me that error: "ActiveX component cannot create the object"

[Kenneth Hobs]

I guess that it might matter what version of Excel and Windows you are using. Attached is one that works for my Windows XPPro and Excel 2010. Both Microsoft XML, v4 and v6 worked for me.

If this file shows Missing in VBE's Tools > References, you may need to download the latest version at Microsoft.

[Paleo]

This is great Kenneth, it worked like a charm!
I saw the difference, it's in here:
[VBA]
'This one did not work
Dim xmlhtp As New MSXML2.XMLHTTP40
[/VBA]

[VBA]
'This one worked like a charm
Dim xmlhtp As New MSXML2.XMLHTTP
[/VBA]

[Kenneth Hobs]

Yes, that made it version specific. In the link that I sent you to, my code was the same as in the attachment. Hopefully, Jan's concept will help you out.

[Paleo]

Many thanks Kenneth and Jan!

[Bogdan0x400]

I know MS recomends you not to use SOAP Toolkit anylonger since Office 2007, but they also recomend you to abandonm VBA, what I wont do. Here you can see the worst recomendation ever: (links removed)
I say worst because you would endup by having to install .Net Framework, plus VSTO tools and on many clients this is simply not an option.

But don't you need to install Web Service Tool Kit on the client's machine if you decide to use it instead of VSTO? Either way you have to instal something. So I don't see a major disadvantage of VSTO here.

[Jan Karel Pieterse]

No, the web service toolkit is a developer toolkit that generates some standalone VBA code for you. The user does not need the toolkit.

[mmazhar]

I have a similar problem of calling webservice from office 2010. The option of using MSXML2.XMLHTTP sounds interesting. I tried as well and it works just fine. However, I have a different situation here.

I need to send file content (file data) as a parameter to web service.

What if I read file data in a string variable and send the data as string parameter to my webservice.

Please tell me if its possible and feasible.

[scott56]

I would like to say thankyou to all those that have posted here
I used your script and it has worked perfectly to resolve a critical problem
In one of our applications.....

[hitami]

I guess that it might matter what version of Excel and Windows you are using. Attached is one that works for my Windows XPPro and Excel 2010. Both Microsoft XML, v4 and v6 worked for me.

If this file shows Missing in VBE's Tools > References, you may need to download the latest version at Microsoft.

Thx, I'm going to try this.