为excel2010创建等值的google doc电子表格函数importxml（）函数

Question

我曾经使用过google电子表格，特别是它们的importxml（）函数。我想知道是否有一种方法可以在VBA中创建函数（对于Excel 2010）（我不是很聪明的在VBA上创建函数），从而可以将google函数复制到excel 2010文件中运行？

我发现一些VBA代码，我认为它可以使用，但我似乎无法运行它给我我想要的。

理想情况下，我想把一个单元格（在Excel 2010中）以下函数并获取xpath内容。即：= importXML（“网址”，“xpath”）

任何指导将不胜感激。

最佳 帕科

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 

Answer 1

，如果你是，你可以使用Neil Bosma's SEO tools运行XPath函数的Windows用户。