how to consume webservice xml in Excel 2000?

[ljb]

I originally posted this in microsoft.public.excel. I think it probably
belongs here.

I currently have Excel 2000 VBA that loads and parses static XML from a
file. How would I convert this to consume the same XML from a webservice? Is
there a replacement for xmldoc.Load(filepath) that will trigger the
webservice?

If xmldoc.Load(filepath) Then
If Not xmldoc.documentElement.selectSingleNode("//INFO") Is Nothing
Then
.......


thanks
LJB

 

[Wei-Dong XU [MSFT]]

Hi LJB,

To process the message from Web Service, we will need to interpret the web service soap message, which is not easy to handle from the ground
up., so for using Web service in Office, Microsoft has released one Web service toolkit for Office. However, they are for Office XP and Office 2003.
If your scenario permits, I'd suggest the best solution is to upgrade to Office XP or 2003. That will be simple for you to develop one solution from
web service. If not, since Office application is un-managed application(their execution doesn't need the common language runtime), I'd suggest
you can use the SOAP toolkit to consume the web service, though it will be replaced by .Net Framework in July 1, 2004. By using this toolkit, you
don't need to interop the managed component to un-managed for VBA.
SOAP Toolkit 3.0
http://www.microsoft.com/downloads/...DD-CEEC-4088-9753-86F052EC8450&displaylang=en
SOAP Toolkit
http://msdn.microsoft.com/library/en-us/dnanchor/html/anch_SOAPToolkit.asp

Furthermore, as I have mentioned, SOAP toolkit will be replaced by .Net Framework. The .Net Framework is the future, so I'd also suggest you can
use the .Net Framework to consume web service. You can develop one component to use the Web Service and expose some interfaces for the
result, then interop this managed component to be used by Office 2000. The links will provide more information for you.
COM Interoperability in Visual Basic and Visual C#
http://msdn.microsoft.com/library/en-us/vbcon/html/vbconcominteropinvisualbasicvisualc.asp

If you choose using .Net framework, you will have to interop your managed component to un-managed for VBA which may spend more time on your
development. However, this will save you a lot of time on the maintenance and upgrade for your office solution since the SOAP toolkit will be
replaced soon and .Net framework provides more feature and utilities for you. Don Box will introduce more for you from this article.
Moving to .NET and Web Services
http://msdn.microsoft.com/msdnmag/issues/01/11/webserv/toc.asp

Please feel free to let me know if you have any further questions. Our pleasure to be of assistance.

Best regards,
Wei-Dong Xu
Microsoft Product Support Services
Get Secure! - www.microsoft.com/security
This posting is provided "AS IS" with no warranties, and confers no rights.

 

[Wei-Dong XU [MSFT]]

Hi LJB,

I have also replied your another issue on this topic in the newsgroup: microsoft.public.scripting.vbscript. The title is: how to consume webservice
from vbscript?. Please feel free to let me know if you have any further question on this.

Wei-Dong Xu
Microsoft Product Support Services
Get Secure! - www.microsoft.com/security
This posting is provided "AS IS" with no warranties, and confers no rights.

 

[ljb]

Thank you for your help. I'm sorry I ended up posting similar questions both
here and in microsoft.public.scripting.vbscript. I found the Soap 3.0 tool
kit yesterday and have installed it. Your suggestion to look at "Code
Listing for the Execute Method" is much appreciated. It does indeed look
like it will provide me a solution.

thanks
LJB

 

[GingerTommy]

One way to do it is to use the XML HTTP Request object, available in
Microsoft's XML library, set a reference to the XML library before
attempting to use. The following example (untested, may need tweaking)
uses version 3. The .responseXml property delivers the payload.


Public Sub TextWebService()
Dim strUrl As String
Dim xhttp As MSXML2.XMLHTTP

strUrl = "http://www.yourServer.com/yourService?param1=x&param2=y"
Set xhttp = New MSXML2.XMLHTTP
With xhttp
.Open "GET", Url, True
.send vbNullString

Do While .readyState <> 4
DoEvents
Loop

Debug.Print .Status, .statusText, .responseXml
End With

Set xhttp = Nothing
End Sub


HTH, Thomas.

 

[ljb]

Indeed the xmlHttp object seems to work quite well.

Set xmlHttp = CreateObject("Msxml2.XMLHTTP.4.0")
Set xmlDoc = CreateObject("Msxml2.DOMDocument.4.0")
strUrl =
"http://www.dev1.eraserver.net/D2S/WebServiceSample/isPrime.asmx/IsPrime?n=1
1"
xmlHttp.Open "GET", strUrl, False
xmlHttp.send
if xmlHttp.Status = 200 then xmlDoc.loadXML(xmlHttp.responseXML.xml)

 

[Wei-Dong XU [MSFT]]

Hi LJB,

You are welcome! Please feel free to let me know if you have any questions.

Kindly regards,
Wei-Dong Xu
Microsoft Product Support Services
Get Secure! - www.microsoft.com/security
This posting is provided "AS IS" with no warranties, and confers no rights.