Creating an equivalent of google doc spreadsheet function importxml() function for excel2010

Question

I have used in the past google spreadsheets, an in particular their importxml() function. I would like to know if there is a way to build a function in VBA (for Excel 2010) (i'm not very savvy creating functions on VBA) whereby the google function could be replicated to be run within an excel 2010 file?

I have found some VBA code that I thought it could be used but I cannot seem to run it to give me what I want.

Ideally, I would like to put on a cell (within excel 2010) the following function and get the xpath content. ie: =importXML("web url","xpath")

Any guidance will be greatly appreciated.

Best Paco

Option Explicit

Public Sub WaitBrowserQuiet(objIE As InternetExplorer)
Do While objIE.Busy Or objIE.ReadyState <> READYSTATE_COMPLETE
    DoEvents
Loop
End Sub


Public Function getXPathElement(sXPath As String, objElement As HTMLBaseElement) As HTMLBaseElement
Dim sXPathArray() As String

Dim sNodeName As String
Dim sNodeNameIndex As String
Dim sRestOfXPath As String
Dim lNodeIndex As Long
Dim lCount As Long

' Split the xpath statement
sXPathArray = Split(sXPath, "/")
sNodeNameIndex = sXPathArray(1)
If Not InStr(sNodeNameIndex, "[") > 0 Then
    sNodeName = sNodeNameIndex
    lNodeIndex = 1
Else
    sXPathArray = Split(sNodeNameIndex, "[")
    sNodeName = sXPathArray(0)
    lNodeIndex = CLng(Left(sXPathArray(1), Len(sXPathArray(1)) - 1))
End If
sRestOfXPath = Right(sXPath, Len(sXPath) - (Len(sNodeNameIndex) + 1))

Set getXPathElement = Nothing
For lCount = 0 To objElement.childNodes().Length - 1
    If UCase(objElement.childNodes().Item(lCount).nodeName) = UCase(sNodeName) Then
        If lNodeIndex = 1 Then
            If sRestOfXPath = "" Then
                Set getXPathElement = objElement.childNodes().Item(lCount)
            Else
                Set getXPathElement = getXPathElement(sRestOfXPath, objElement.childNodes().Item(lCount))
            End If
        End If
        lNodeIndex = lNodeIndex - 1
    End If
Next lCount
End Function




Private Sub cmdGetQuote_Click()
Dim ie As InternetExplorer
Dim elem As HTMLBaseElement
Dim url As String

url = "http://www.bloomberg.com/quote/MXIBTIIE:IND"

Set ie = New InternetExplorer
ie.Visible = True
ie.Navigate url
WaitBrowserQuiet ie

Set elem = getXPathElement("//span[@class=' price']", ie.Document)
Range("A1").Value = elem.innerText

Set ie = Nothing
End Sub

Are you trying to extract this info? `Dow-13,060.10,S&P 500-1,398.08,Nasdaq-3,080.50` — Siddharth Rout, Apr 07 '12 at 13:48
that is strange. if you go to http://www.bloomberg.com/quote/MXIBTIIE:IND the most recent price for Mexico Interbank TIIE 28 Day is 4.76000 (just before As of 04/04/2012). see it? — macutan, Apr 08 '12 at 15:42
Ah I see what you mean. I was looking at the market snapshot :) — Siddharth Rout, Apr 08 '12 at 17:36
There is no equivalet in VBA Excel AFAIK. However one can write a small macro to extract almost any piece of info from a website. If you have ever done extraction using VBA before then this post will actually help you create one such function. http://stackoverflow.com/questions/8798260/html-parsing-of-cricinfo-scorecards/8846791#8846791 — Siddharth Rout, Apr 08 '12 at 17:44

score 1 · Answer 1 · answered Feb 05 '13 at 12:03

1

if you are a windows user you can use Neil Bosma's SEO tools to run Xpath functions.

answered Feb 05 '13 at 12:03

Jeremy McDonald

31
1

Creating an equivalent of google doc spreadsheet function importxml() function for excel2010

1 Answers1