Using VBA To Import XML website into Access

Question

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

1. Download the XML from the web page http://www.ecb.europa.eu/stats/eurofxref/eurofxref-daily.xml
2. 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

Answer 1

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"))