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?
- 1I 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.Mostlyharmless– Mostlyharmless2009-01-23 22:51:59 +00:00Commented Jan 23, 2009 at 22:51
- You might find this useful: stackoverflow.com/questions/3521876/…SAL– SAL2012-07-22 20:34:50 +00:00Commented Jul 22, 2012 at 20:34
Add a comment |
5 Answers
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)
2 Comments
user219095
And just in case anyone is wondering: No, this isn’t available on reasonably recent Excel versions. :(
W4t3randWind
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 @Mostlyharmless originally commented.
Here's an overview from MS:
1 Comment
Justin Dearing
The question doesn't specify which version of excel is being used. However, VBA is being deprecated in 2007 and your method is correct.
For an updated answer see this SO question:
calling web service using VBA code in excel 2010
Both threads should be merged though.
Comments
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 
Comments
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 