功能类似于 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.
例如,我希望能够设置一个标题为项目名称"的列,然后让下一列将上一列中用户输入的项目名称附加到此 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.这是在谷歌文档中使用
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屋!