ASP.NET w-s ... consuming from Excel ?

Question

We have a web-service within an existing ASP.NET website which works fine when accessed via other ASP.NET bits. I'd like to get Excel 2007 and 2003 to call the w-s and refresh part of a worksheet with the results using VBA.

Ideally I'd like a vanilla version of Excel to be able to do this (ie without the client having to install extra bits).

As a starter (in 2007) I tried Data->Get External Data->From Web. Pointed it at : http://myhost/myvirtdir/ABCInfoWS.asmx?WSDL&op=testwebservice1

Stuff appears in Excel (albeit with 'The Specified XML source does not refer to a schema' message) but it turns out that it's actually a description of all web-services offered under the same WSDL.

Can anyone tell me how I can get the data from testwebservice via VBA ?

Answer 1

Remove the ?WSDL part from the URL above.

EDIT: What does your webservice return?
Excel's Data -> Import Data expects a tabular structure built with tr & td to get the data in.

If your webservice returns XML, it should work as well.

Answer 2

Sorry it turns out I can't provide enough infromation via comments. This is what is appearing in Excel when I select SOAP 2 and XML:

Just responding to your edits my w-s returns XML and this is what it looks like - it doesn't contain tr/td because it's previously been used as a 'real' webservice .

I've realised that I'm not getting the actual data at all but instead a description of the data that the webservice provides - I guess my URL is still off the beat a bit ?

<?xml version="1.0" encoding="utf-8"?>
<soap12:Envelope xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:soap12="http://www.w3.org/2003/05/soap-envelope"&gt;
<soap12:Body>
<GetBookJobStatusResponse xmlns="http://www.protecttheguilty.com/"&gt;
<GetBookJobStatusResult>
<BookJobStatus>
<JobStatus>string</JobStatus>
<JobType>string</JobType>
<RequestTime>dateTime</RequestTime>
<StartTime>dateTime</StartTime>
<EndTime>dateTime</EndTime>
<PathToOutput>string</PathToOutput>
</BookJobStatus>
<BookJobStatus>
<JobStatus>string</JobStatus>
<JobType>string</JobType>
<RequestTime>dateTime</RequestTime>
<StartTime>dateTime</StartTime>
<EndTime>dateTime</EndTime>
<PathToOutput>string</PathToOutput>
</BookJobStatus>
</GetBookJobStatusResult>
</GetBookJobStatusResponse>
</soap12:Body>
</soap12:Envelope>