功能类似于Excel中的importxml吗? [英] Function Similar to importxml in Excel?

问题描述

我喜欢使用Google Docs函数= importxml()，但很想知道Excel 2010中是否有类似的东西?我似乎找不到该程序从链接的XML文件中自动提取数据的方法.

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.

例如，我希望能够设置标题为"Item Name"的列，然后将下一列的前一列中用户输入的项目名称附加到此url

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=

，然后解析生成的XML文件以返回类型ID.这是在Google文档中使用

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是具有项目名称的列，在本例中为Tritanium，并从生成的XML文件中导入数据

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

返回值34.

我有大约20个项目名称的列表，每次我打开文件时，谷歌文档都会自动更新该项目ID. Excel 2010有什么方法可以复制此功能?

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?

谢谢！

会

推荐答案

您将需要编写自己的UDF.

You will need to write your own UDF.

一种方法是使用MSXML2库，如下所示:

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

这样命名(其中A5包含必需的typeName)

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

=GetData(A5, "typeID")

这篇关于功能类似于Excel中的importxml吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！