Geek asks and answers

Using VBA to import an XML site into Access

I want to download exchange rates from this site weekly using VBA. I am very new to XML and looked back at the exchange stack and saw several implementations that use the form (I want to avoid this method)

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

I would like to complete these steps if possible

I currently have the code below. But it is, obviously, compiled on the basis of the work of other peoples and is more likely a template for work than anything else. Can someone 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
+3
source share
1 answer

Use XPath to select the elements you getAttributewant , and then to extract values ​​for the attributes currencyand ratefrom 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+ gto go there. Here is a shortened output sample ...

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

, Debug.Print . , , getAttribute . rate , . Single, .

CSng(xmlElement.getAttribute("rate"))
+3

More articles:


All Articles