vba 尝试通过excel从网站检索xml数据
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/13579410/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Trying to retrieve xml data from a website through excel
提问by Venoushka
I am writing an excel spreadsheet for use with a game called Eve Online. My spreadsheet needs to automatically retrieve data through a data provider website called eve-marketdata.com. On the developer page of eve-marketdata: http://eve-marketdata.com/developers/item_history2.phpit gives some instructions on how to request data by sending a 'post' request through http.
我正在编写一个 Excel 电子表格，用于名为 Eve Online 的游戏。我的电子表格需要通过名为 eve-marketdata.com 的数据提供商网站自动检索数据。在 eve-marketdata 的开发者页面：http://eve-marketdata.com/developers/item_history2.php它给出了一些关于如何通过 http 发送“发布”请求来请求数据的说明。
For instance: The url used for a market history request would be: http://api.eve-marketdata.com/api/item_history2.xml?char_name=venoushkapatel then, lets say I want to retrieve 45 days of data for item id 34, from region 10000002. I would put at the end of the url: item_id=34, days=45 and region_ids=10000002 each separated by the & symbol for a finished product of: api.eve-marketdata.com/api/item_history2.xml?char_name=venoushka patel&type_ids=34®ion_ids=10000002&days=45 (removed the http:// due to posting regulations on this site)
例如：用于市场历史请求的 url 将是：http://api.eve-marketdata.com/api/item_history2.xml ?char_name=venoushka patel 那么，假设我想检索项目的 45 天数据id 34，来自区域 10000002。我会在 url 的末尾放置：item_id=34, days=45 和 region_ids=10000002，每个都用 & 符号分隔，表示成品：api.eve-marketdata.com/api/ item_history2.xml?char_name=venoushka patel&type_ids=34®ion_ids=10000002&days=45（因本站发帖规定去掉了http://）
So, I need certain cells to use the info in the spreadsheet such as item ids, region ids, etc, to parse together the appropriate url, submit the url to the site and then grab only the info that I am looking for and place it into a cell. This is currently much easier on google docs instead of excel because of their 'importxml' feature utilizing xpath. However, google docs limit the number of xml calls to 50 per spreadsheet and my spreadsheet is going to have a few thousand, so I HAVE to use excel.
因此，我需要某些单元格来使用电子表格中的信息（例如项目 ID、区域 ID 等）来一起解析适当的 url，将 url 提交到站点，然后仅获取我正在查找的信息并将其放置进入一个单元格。由于使用 xpath 的“importxml”功能，这目前在 google docs 而不是 excel 上要容易得多。但是，谷歌文档将每个电子表格的 xml 调用次数限制为 50 个，而我的电子表格将有几千个，所以我必须使用 excel。
And no, I am not going to use the 'import from web' or 'map xml' to cells features in excel because I have thousands of these to do in a spreadsheet, not time or resource efficient.
不，我不会在 Excel 中使用“从 Web 导入”或“映射 xml”到单元格功能，因为我在电子表格中有数千个这样的功能，而不是时间或资源效率。
I've been told that this can be done using the following vba code:
有人告诉我，这可以使用以下 vba 代码来完成：
Set objHTTP = CreateObject("MSXML2.ServerXMLHTTP")
URL = "http://www.somedomain.com"
objHTTP.Open "POST", URL, False
objHTTP.setRequestHeader "User-Agent", "Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.0)"
objHTTP.send ("")
The problem is, although i'm very good at programing excel spreadsheets, I have never used VBA before. I know how to open the visual basic editor but that is about it. First off:
问题是，虽然我非常擅长编写 excel 电子表格，但我以前从未使用过 VBA。我知道如何打开 Visual Basic 编辑器，但仅此而已。首先：
How would I paste this into the vba editor and then call upon it in my workbook? I believe I would have to put something like function () at the beginning and end function at the end, right?
我如何将它粘贴到 vba 编辑器中，然后在我的工作簿中调用它？我相信我必须在开头放函数 () 并将结束函数放在最后，对吗？
How would I call upon this function in my worksheet to do what I need it to do?
我如何在我的工作表中调用这个函数来做我需要它做的事情？
Could someone explain what the above code does, and how I need to modify it to perform my task?
有人能解释一下上面的代码是做什么的，以及我需要如何修改它来执行我的任务吗？
In advance, I REALLY APPRECIATE any help received on this and there is a huge part of the community (thousands out there) that need a way to do this as well!
在此之前，我真的很感激收到的任何帮助，并且社区中有很大一部分（成千上万的人）也需要一种方法来做到这一点！
回答by SWa
You wouldn't do it quite like that. Firstly, the request is GET and not POST, so that changes things. You can use something like the below:
你不会那样做的。首先，请求是 GET 而不是 POST，所以这会改变一些事情。您可以使用以下内容：
Function ImportData(sUrl As String) As Variant
Dim oDoc As Object
Dim oAttr As Object
Dim oRow As Object
Dim x As Long: x = 1
Dim y As Long: y = 1
Dim vData() As Variant
Set oDoc = CreateObject("MSXML2.DOMDocument")
With oDoc
.Load sUrl
Do: DoEvents: Loop Until .readystate = 4
ReDim vData(1 To .getElementsByTagName("row").Length, 1 To 8)
For Each oRow In .getElementsByTagName("row")
For Each oAttr In oRow.Attributes
vData(x, y) = oAttr.Text
y = y + 1
Next oAttr
y = 1
x = x + 1
Next oRow
End With
ImportData = vData
End Function
Public Sub Test()
Dim g
'g = ImportData("http://api.eve-marketdata.com/api/item_history2.xml?char_name=venoushka%20patel&type_ids=34®ion_ids=10000002&days=45")
'or
g = ImportData(Sheets(1).range("a1").Value)
Sheets(1).Cells(2, 1).Resize(UBound(g), UBound(g, 2)).Value = g
End Sub
You can test it by putting it in a normal module then running the macro Testyou can see where you'd put your url. I have to admit though, you might be up for a steep learning curve with this one, it's not going to be the easiest project to cut your VBA teeth on
您可以通过将它放在普通模块中然后运行宏来测试它，Test您可以看到您将网址放在哪里。不过，我不得不承认，你可能会面临陡峭的学习曲线，这不是削减 VBA 牙齿的最简单项目
