Using VBA To Import XML website into Access
ms-access, vba, xml
Solution
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"))
Problem
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 ```