使用 VBA 将 XML 网站导入 Access
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/21580868/
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
Using VBA To Import XML website into Access
提问by John Smith
I wish to download the exchange rates from this website on a weekly basis using VBA I am very new to XML and have been looking around on stack exchange and have seen a few implementations that use a form (i want to avoid this method)
我希望使用 VBA 每周从本网站下载汇率我对 XML 非常陌生，并且一直在寻找堆栈交换，并且已经看到了一些使用表单的实现（我想避免这种方法）
I have tried to import it using MS Access Wizard but all the fields in the tables are blank
我曾尝试使用 MS Access Wizard 导入它，但表中的所有字段均为空白
I would like to implement these steps if possible
如果可能，我想实施这些步骤
- Download the XML from the web page http://www.ecb.europa.eu/stats/eurofxref/eurofxref-daily.xml
- Loop through the XML and place the currency and the exchange rate into either a new or existing two column table
- 从网页http://www.ecb.europa.eu/stats/eurofxref/eurofxref-daily.xml下载 XML
- 遍历 XML 并将货币和汇率放入新的或现有的两列表中
Currently i have the below code. But its obviously put together based on other peoples work and is more a template to work off of than anything else Can anyone point me in the right direction
目前我有以下代码。但它显然是根据其他人的工作组合在一起的，而且比其他任何东西都更像是一个工作模板，谁能指出我正确的方向
Sub Test()
'**********************************************************
' DOWNLOAD XML DATA
' ref: http://stackoverflow.com/questions/7091162/access-vba-how-to-download-xml-file- and-enter-its-data-into-a-recordset
'**********************************************************
Dim obj As MSXML2.ServerXMLHTTP
Set obj = New MSXML2.ServerXMLHTTP
obj.Open "GET", "http://www.ecb.europa.eu/stats/eurofxref/eurofxref-daily.xml", False
'in case you are sending a form *POST* or XML data to a SOAP server set content type
obj.setRequestHeader "Content-Type", "text/xml"
obj.send
Dim status As Integer
status = obj.status
If status >= 400 And status <= 599 Then
Debug.Print "Error Occurred : " & obj.status & " - " & obj.statusText
End If
'**********************************************************
'CREATE XML DOM DOCUMENT
'**********************************************************
Dim xmlDoc As MSXML2.DOMDocument
Dim xmlElement As MSXML2.IXMLDOMElement
Dim xmlNode As MSXML2.IXMLDOMElement
Set xmlDoc = New MSXML2.DOMDocument
xmlDoc.loadXML (obj.responseText)
'**********************************************************
'ACCESS ROWS
'http://stackoverflow.com/questions/11305/how-to-parse-xml-in-vba
'**********************************************************
Dim point As IXMLDOMNode
Set point = xmlDoc.firstChild
Debug.Print point.selectSingleNode("subject").Text
End Sub
采纳答案by HansUp
Use XPath to select the elements you want and then getAttributeto extract the values for the currencyand rateattributes from each selected element.
使用 XPath 选择您想要的元素，然后从每个选定元素中getAttribute提取currency和rate属性的值。
Const cstrXPath As String = "/gesmes:Envelope/Cube/Cube/Cube"
Dim xmlDoc As MSXML2.DOMDocument
Dim xmlElement As MSXML2.IXMLDOMElement
Dim xmlSelection As MSXML2.IXMLDOMSelection
Dim i As Long
Dim strUrl As String
strUrl = "http://www.ecb.europa.eu/stats/" & _
"eurofxref/eurofxref-daily.xml"
Set xmlDoc = New MSXML2.DOMDocument
xmlDoc.async = False
xmlDoc.Load strUrl
Set xmlSelection = xmlDoc.SelectNodes(cstrXPath)
Debug.Print "xmlSelection.Length: " & xmlSelection.Length
i = 1
For Each xmlElement In xmlSelection
Debug.Print i, xmlElement.getAttribute("currency"), _
xmlElement.getAttribute("rate")
i = i + 1
Next xmlElement
You can view the output in the Immediate window; you can use Ctrl+gto go there. Here is an abbreviated output sample ...
您可以在立即窗口中查看输出；你可以使用Ctrl+g去那里。这是一个缩写的输出示例......
xmlSelection.Length: 32
1 USD 1.3495
2 JPY 136.93
3 BGN 1.9558
Ultimately you want to store those values, not just Debug.Printthem. When you get to that point, notice getAttributereturns text values. If you will be storing ratein a numeric field, eg. Single, you can transform the text value to a number when you store it.
最终，您希望存储这些值，而不仅仅是Debug.Print它们。当你到达那个点时，notice 会getAttribute返回文本值。如果您将存储rate在数字字段中，例如。单个，存储时可以将文本值转换为数字。
CSng(xmlElement.getAttribute("rate"))
