我想从该网站下载的汇率上使用VBA每周 我是很新的XML和一直在寻找周围堆栈交换和已经看到，使用一种形式（我想避免这种方法）的几个实施

我曾尝试使用的MS Access向导，但将其导入所有的表中的字段为空

我想实现这些步骤如果可能的话

从网络下载的网页。 HTTP的XML：//www.ecb .europa.eu /统计/ eurofxref / eurofxref-daily.xml 通过XML环，然后将货币和汇率变成一个新的或现有的两列的表

目前我有以下code。但它显然是放在一起的基础上其他国家人民的工作，更是一个模板，工作过的比什么都重要 任何人都可以点我在正确的方向。

 子测试（）

******************* *********
下载XML数据
REF：http://stackoverflow.com/questions/7091162/access-vba-how-to-download-xml-file-和输入，其数据-到-A-记录
******************* *********

昏暗OBJ时MSXML2.ServerXMLHTTP
设置OBJ =新MSXML2.ServerXMLHTTP

obj.OpenGET，http://www.ecb.europa.eu/stats/eurofxref/eurofxref-daily.xml，假
如果您要发送的形式* POST *或XML数据到SOAP服务器集内容类型
obj.setRequestHeader内容类型，为text / xml
obj.send

昏暗的状态作为整数
状态= obj.status

如果状态＆gt; = 400和状态＆LT; = 599然后
    Debug.Print发生错误：＆放大器; obj.status和放大器;  - ＆安培; obj.statusText
结束如果

   ******************* *********
   创建XML DOM文档
   ******************* *********

昏暗的xmlDoc中，由于MSXML2.DOMDocument
昏暗XMLELEMENT作为MSXML2.IXMLDOMElement
昏暗的xmlNode作为MSXML2.IXMLDOMElement
设置xmlDoc中=新MSXML2.DOMDocument
xmlDoc.loadXML（obj.responseText）

   ******************* *********
   访问行
   http://stackoverflow.com/questions/11305/how-to-parse-xml-in-vba
   ******************* *********

昏暗的点作为IXMLDOMNode
设定值= xmlDoc.firstChild

Debug.Print point.selectSingleNode（主题）。文本

结束小组
 

解决方案

使用XPath来选择所需的元素，然后的getAttribute 来提取值的货币和率，从各个所选元素的属性。

常量cstrXPath的String =/ GESMES：信封/立方/立方/立方 昏暗的xmlDoc中，由于MSXML2.DOMDocument 昏暗XMLELEMENT作为MSXML2.IXMLDOMElement 昏暗xmlSelection作为MSXML2.IXMLDOMSelection 昏暗我只要 昏暗strUrl作为字符串 strUrl =htt​​p://www.ecb.europa.eu/stats/与＆amp; _     eurofxref / eurofxref，daily.xml 设置xmlDoc中=新MSXML2.DOMDocument xmlDoc.async = FALSE xmlDoc.Load strUrl 设置xmlSelection = xmlDoc.SelectNodes（cstrXPath） Debug.PrintxmlSelection.Length：与＆amp; xmlSelection.Length I = 1 对于每一个XMLELEMENT在xmlSelection     Debug.Print我，xmlElement.getAttribute（货币），_         xmlElement.getAttribute（速度）     I = I + 1 接下来XMLELEMENT

您可以查看立即窗口的输出;你可以使用控制 + 先按g 去那里。下面是一个简短的输出采样...

xmlSelection.Length：32  1 USD 1.3495  2 JPY 136.93  3 BGN 1.9558

最终要存储这些值，而不仅仅是 Debug.Print 他们。当你到这一点，通知的getAttribute 返回文本值。如果你要存储率在数字领域，例如。单，您可以将文本值一些，当你保存它。

CSng函数（xmlElement.getAttribute（速度））

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)

I have tried to import it using MS Access Wizard but all the fields in the tables are blank

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

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

解决方案

Use XPath to select the elements you want and then getAttribute to extract the values for the currency and rate attributes from each selected element.

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+g to go there. Here is an abbreviated output sample ...

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.Print them. When you get to that point, notice getAttribute returns text values. If you will be storing rate in a numeric field, eg. Single, you can transform the text value to a number when you store it.

CSng(xmlElement.getAttribute("rate"))