Call web service in excel

Question

In a VBA module in excel 2007, is it possible to call a web service? If so, any code snippets? How would I add the web reference?

Vezza

I know it is possible, because I once worked on a project that did that. Unfortunately though, I only worked on the Web Service part, and had too much on my plate to be able to explore the Excel code. I will try to find out how though. It IS possible. – Sapota

You might find this useful: #3522376 – Gemstone

Answer 1

Yes You Can!

I worked on a project that did that (see comment). Unfortunately no code samples from that one, but googling revealed these:

How you can integrate data from several Web services using Excel and VBA

STEP BY STEP: Consuming Web Services through VBA (Excel or Word)

VBA: Consume Soap Web Services

Sapota

And just in case anyone is wondering: No, this isn’t available on reasonably recent Excel versions. :( – Reconciliatory

And just in case anyone is wondering, web service calls are part of VBA, which is available in recent versions, it's part of Excel's development suite, hidden by default. It's still icky and most of those links were like 10 years old at the time @Sapota originally commented. – Pin

Answer 2

Here's an overview from MS:

Consuming Web Services in Excel 2007

Declare

The question doesn't specify which version of excel is being used. However, VBA is being deprecated in 2007 and your method is correct. – Eade

Answer 3

For an updated answer see this SO question:

calling web service using VBA code in excel 2010

Both threads should be merged though.

Bankable

Answer 4

In Microsoft Excel Office 2007 try installing "Web Service Reference Tool" plugin. And use the WSDL and add the web-services. And use following code in module to fetch the necessary data from the web-service.

Sub Demo()
    Dim XDoc As MSXML2.DOMDocument
    Dim xEmpDetails As MSXML2.IXMLDOMNode
    Dim xParent As MSXML2.IXMLDOMNode
    Dim xChild As MSXML2.IXMLDOMNode
    Dim query As String
    Dim Col, Row As Integer
    Dim objWS As New clsws_GlobalWeather

    Set XDoc = New MSXML2.DOMDocument
    XDoc.async = False
    XDoc.validateOnParse = False
    query = objWS.wsm_GetCitiesByCountry("india")

    If Not XDoc.LoadXML(query) Then  'strXML is the string with XML'
        Err.Raise XDoc.parseError.ErrorCode, , XDoc.parseError.reason
    End If
    XDoc.LoadXML (query)

    Set xEmpDetails = XDoc.DocumentElement
    Set xParent = xEmpDetails.FirstChild
    Worksheets("Sheet3").Cells(1, 1).Value = "Country"
    Worksheets("Sheet3").Cells(1, 1).Interior.Color = RGB(65, 105, 225)
    Worksheets("Sheet3").Cells(1, 2).Value = "City"
    Worksheets("Sheet3").Cells(1, 2).Interior.Color = RGB(65, 105, 225)
    Row = 2
    Col = 1
    For Each xParent In xEmpDetails.ChildNodes
        For Each xChild In xParent.ChildNodes
            Worksheets("Sheet3").Cells(Row, Col).Value = xChild.Text
            Col = Col + 1
        Next xChild
        Row = Row + 1
        Col = 1
    Next xParent
End Sub

Hangchow

Answer 5

Excel 2013 Read Data from a web service and bash the JSON till you can get what you want out of it (given the JSON will always be in the same format).

This code should just work without the need for any plugins.

You will need your own free API key from the currency converter website though.

I used it to load the USD to GBP value into a cell on my sheet.

Option Explicit

Sub Test_LateBinding()

    Dim objRequest As Object
    Dim strUrl As String
    Dim blnAsync As Boolean
    Dim strResponse As String

    Set objRequest = CreateObject("MSXML2.XMLHTTP")
    strUrl = "https://free.currconv.com/api/v7/convert?q=USD_GBP&compact=ultra&apiKey=[MY_API_KEY]"
    blnAsync = True

    With objRequest
        .Open "GET", strUrl, blnAsync
        .SetRequestHeader "Content-Type", "application/json"
        .Send
        While objRequest.readyState <> 4
            DoEvents
        Wend
        strResponse = .responsetext
    End With
    
    Dim responseArray() As String
    responseArray = Split(strResponse, ":", -1)
    
    Dim value As String
    value = responseArray(1)
    
    Dim valueArray() As String
    valueArray = Split(value, "}", -1)
    
    Dim finalValue As String
    finalValue = valueArray(0)
    
    Sheet2.Cells(22, "C") = finalValue
End Sub

Poster