Reputation: 46360

Call web service in excel

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?

Upvotes: 28

Answers (5)

Andy

Reputation: 2354

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

Upvotes: 0

Adnan Shahmir Ahmed

Reputation: 11

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

Upvotes: 1