Function Similar to importxml in Excel?

I love using Google Docs function =importxml() but would love to know if there was anything like it in Excel 2010? I cant seem to find a way for the program to automatically pull data from a linked XML file.

For example, I would love to be able to set up a column with the header "Item Name", then have the next column append the user-entered item name in the previous column to this url

http://util.eveuniversity.org/xml/itemLookup.php?name=

and then parse the resulting XML file to return the type ID. This is accomplished in google docs using

=importxml(concatenate("http://util.eveuniversity.org/xml/itemLookup.php?name=",A3);"//itemLookup/typeID")

A3 is the column that has the item name, which in this case would be Tritanium, and imports the data form the resulting XML file

http://util.eveuniversity.org/xml/itemLookup.php?name=Tritanium

which returns the value 34.

I have a list of about 20 item names that google docs automatically updates the item ID on every time I open the file. Is there any way for Excel 2010 to replicate this function?

Thanks!

Will

Solution

You will need to write your own UDF.

One way would be to use the MSXML2 library, something like this:

Function GetData(sName As String, sItem As String, Optional sURL = "") As Variant
    Dim oHttp As New MSXML2.XMLHTTP60
    Dim xmlResp As MSXML2.DOMDocument60
    Dim result As Variant
    On Error GoTo EH

    If sURL = "" Then
        sURL = "http://util.eveuniversity.org/xml/itemLookup.php?name="
    End If

    'open the request and send it'
    oHttp.Open "GET", sURL & sName, False
    oHttp.Send

    'get the response as xml'
    Set xmlResp = oHttp.responseXML
    ' get Item'
    GetData = xmlResp.getElementsByTagName(sItem).Item(0).Text

    ' Examine output of these in the Immediate window'
    Debug.Print sName
    Debug.Print xmlResp.XML

CleanUp:
    On Error Resume Next
    Set xmlResp = Nothing
    Set oHttp = Nothing
Exit Function
EH:
    GetData = CVErr(xlErrValue)
    GoTo CleanUp
End Function

Call it like this (where A5 contains the required typeName)

=GetData(A5, "typeID")