1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70
| Function ImportXmlEurofXref()
' utilise ref VBA : Microsoft XML, version 2 ...\system32\msxml.dll
Dim xmlDoc As MSXML.DOMDocument, xmlCube As MSXML.IXMLDOMNode, xmlNode As MSXML.IXMLDOMNode
Dim db As DAO.Database, rXrates As DAO.Recordset
Dim strCur As String, strRate As String, curRate As Currency
Dim strDecCar As String, strThCar As String, strTstFmt As String
strTstFmt = Format(1234.5678, "#,##0.0000")
If Len(strTstFmt) = 10 Then
strThCar = Mid(strTstFmt, 2, 1)
Else
strThCar = ""
End If
strDecCar = Mid(strTstFmt, Len(strTstFmt) - 4, 1)
Set xmlDoc = New MSXML.DOMDocument
' Charger ficher xml
xmlDoc.Load "http://www.ecb.int/stats/eurofxref/eurofxref-daily.xml"
' Wait
While (xmlDoc.parsed = False)
DoEvents
Wend
' Format fichier xml :
' ---------------------------------------------------
' <Cube>
' <Cube time="2009-03-05">
' <Cube currency="USD" rate="1.2555"/>
' ....
' <Cube currency="ZAR" rate="13.2079"/>
' </Cube>
' </Cube>
' Obtenir premier élément "Cube" dans le document
Set xmlCube = xmlDoc.documentElement.SelectSingleNode("Cube")
' Elément "Cube" de niveau inférieur
Set xmlCube = xmlCube.SelectSingleNode("Cube")
Set db = CurrentDb
Set rXrates = db.OpenRecordset("Xrates", dbOpenDynaset)
' Boucler sur les noeuds inférieurs (<Cube currency="CCC" rate="n.nnnn"/>)
' et récupérer les attributs "currency" et "rate".
' Le taux est EUR/DEVISE (1 EUR = n.nnnn CCC)
For Each xmlNode In xmlCube.ChildNodes
strCur = xmlNode.Attributes.getNamedItem("currency").NodeValue
strRate = xmlNode.Attributes.getNamedItem("rate").NodeValue
strRate = Replace(strRate, ".", strDecCar)
curRate = CCur(strRate)
' Rechercher la devise dans la table et la mettre à jour
rXrates.FindFirst "Devise=""" & strCur & """"
If Not rXrates.NoMatch And curRate <> 0 Then
rXrates.Edit
rXrates("EUR2DEV") = curRate
rXrates.Update
End If
Next
ENDP:
rXrates.Close
db.Close
Set xmlCube = Nothing
Set xmlDoc = Nothing
End Function |
Partager