Consuming webservice in excel and deploying as one file

I would like to fetch some data into my Excel Spreadsheet (Excel 2007) from webservice, but I would like to deploy the spreadsheet as one file only (f.e. spreadsheet.xlsx - nothing more).

Without this constraint I would use Visual Studio addons and write it in C#, but it would give me some extra dlls and vsto files.

In earlier version of excel, there was Webservices Tolkit, but my research indica开发者_Go百科te, that it won't work with 2007.

Are there any solutions out there? I heard something about Microsoft Office Soap Type library 3.0, but I don't know how to start working with it.

Any help / sample code / other solutions will be appreciated.

I find out how to connect with webservice with Ms Office Soap Type library in vba - so no extra files, just xls(x). At this point I know how to get simple datatypes results (like string). But I hope, I'll be able to get and work with more complex types.

Here's the code:

Dim webservice As SoapClient30
Dim results As String

' Point the SOAP API to the web service that we want to call...
Set webservice = New SoapClient30
Call webservice.mssoapinit(par_WSDLFile:="{url to wsdl}")

' Call the web service
results = webservice.{method name}()
Set webservice = Nothing

It's necessary to add "Microsoft Office Soap Type Library v3.0" to your worksheet references.

Sorry to dig up an old post, but I had a similar constraint recently with everything needing to be contained to a single workbook and want to post my solution in case anyone has a similar issue. Ended up writing my own all-VBA library (based heavily on one of my favorites, RestSharp).

Warning, shameless plug: https://github.com/timhall/Excel-REST

Some fun features include authentication (Http Basic, OAuth1, and OAuth2 Client Credentials), Async support, and JSON parsing (thanks to vba-json)

It's working awesomely in Excel 2010 (and most likely 2007), but does not work on Excel for Mac due to missing XMLHTTP libraries. I've got it working with Salesforce, Trello, Basecamp, Google Maps, and it should work with pretty much any REST webservice.

Example:

Function GetDirections(Origin As String, Destination As String) As String
    ' Create a RestClient for executing requests
    ' and set a base url that all requests will be appended to
    Dim MapsClient As New RestClient
    MapsClient.BaseUrl = "https://maps.googleapis.com/maps/api/"

    ' Create a RestRequest for getting directions
    Dim DirectionsRequest As New RestRequest
    DirectionsRequest.Resource = "directions/{format}"
    DirectionsRequest.Method = httpGET

    ' Set the request format -> Sets {format} segment, content-types, and parses the response
    DirectionsRequest.Format = json

    ' (Alternatively, replace {format} segment directly)
    DirectionsRequest.AddUrlSegment "format", "json"

    ' Add parameters to the request (as querystring for GET calls and body otherwise)
    DirectionsRequest.AddParameter "origin", Origin
    DirectionsRequest.AddParameter "destination", Destination

    ' Force parameter as querystring for all requests
    DirectionsRequest.AddQuerystringParam "sensor", "false"

    ' => GET https://maps.../api/directions/json?origin=...&destination=...&sensor=false

    ' Execute the request and work with the response
    Dim Response As RestResponse
    Set Response = MapsClient.Execute(DirectionsRequest)

    If Response.StatusCode = 200 Then
        ' Work directly with parsed json data
        Dim Route As Object
        Set Route = Response.Data("routes")(1)("legs")(1)

        GetDirections = "It will take " & Route("duration")("text") & _
            " to travel " & Route("distance")("text") & _
            " from " & Route("start_address") & _
            " to " & Route("end_address")
    Else
        GetDirections = "Error: " & Response.Content
    End If
End Function

Have you tried http://msdn.microsoft.com/en-us/library/dd819156(v=office.12).aspx