Darren
darren at activebilling.com.au
Wed Jun 27 22:28:15 CDT 2012
Wow - That's way cool.
Many thanks for the effort Jack, I'm humbled and grateful.
Re me adapting it for my needs - Me thinks you assume too much <grin>
Again my many thanks for all the effort you've put in. It's very slick.
I will be playing with it.
Darren
-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of jack drawbridge
Sent: Thursday, 28 June 2012 10:58 AM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] Exchange Rate - Currency Converter thingy
Darren,
I have created some vba code to get exchange rates from the European Central
Bank.
I have spent a couple of hours trying to learn enough xmlhttp and
DomDocument to simplify coding.
Here is latest effort which you could adapt to your needs I'm sure.
Jack
'---------------------------------------------------------------------------
------------
' Procedure : LeeExchangeRate
' Author : Jack
' Date : 27-06-2012
' Purpose : To get the daily exchange rates from the European Central Bank
' at http://www.ecb.int/stats/eurofxref/eurofxref-daily.xml
' These currency/rate pairs show the value of 1 euro in this related
currency on this date '
' From a learning view, this is sending an XMLHTTP request and getting a
Response and ' using the DOMDocument amd related objects to extract the
required data ' from the xml response. This approach seems cleaner and more
consistent than using ' vba functions to search and parse strings within the
response.
'---------------------------------------------------------------------------
------------
' Last Modified:
'
' Inputs: N/A
' Dependency:
' **********This requires a reference to Microsoft XML, v6.0 **************
'--------------------------------------------------------------------------
'
Sub LeeExchangeRate()
Dim Resp As New DOMDocument
Dim Req As New XMLHTTP
Dim objNodeList As IXMLDOMNodeList
Dim objNode As IXMLDOMNode
Dim objAttribute As IXMLDOMAttribute
Dim mCurrency As String
Dim mRate As String
Dim x As Integer
On Error GoTo LeeExchangeRate_Error
Req.Open "GET", "http://www.ecb.int/stats/eurofxref/eurofxref-daily.xml",
False
Req.send
Resp.loadXML Req.responseText
' uncomment the next line to see the response
' Debug.Print "XML Response is " & vbCrLf & "~~~~~~~~~~~~~~~~~" & vbCrLf &
Req.responseText
x = Resp.getElementsByTagName("Cube").Length
Debug.Print "The number of <Cube nodes is " & x
'
' Get all Cube nodes.
'
Set objNodeList = Resp.getElementsByTagName("Cube")
'
' For each cube node if it has attributes currency and rate then display
the values.
' One Cube row has no attributes and one has only a time attribute.
'
For Each objNode In objNodeList
If (objNode.Attributes.Length > 0) Then
' Get the "time" attribute for the date to which these exchange rates
apply
Set objAttribute = objNode.Attributes.getNamedItem("time")
If (Not objAttribute Is Nothing) Then
Debug.Print "Exchange Rates from European Central Bank as
at " & objAttribute.text & vbCrLf _
& vbCrLf & "**Read these as 1 euro = **" & vbCrLf
End If
' Get the "currency" attribute
Set objAttribute = objNode.Attributes.getNamedItem("currency")
If (Not objAttribute Is Nothing) Then
mCurrency = objAttribute.text
End If
' Get the associated "rate" attribute
Set objAttribute = objNode.Attributes.getNamedItem("rate")
If (Not objAttribute Is Nothing) Then
mRate = objAttribute.text
End If
'Put the data in my variables for display
If mCurrency > " " And mRate > " " Then
Debug.Print mCurrency & " " & mRate
End If
End If
Next objNode
On Error GoTo 0
Exit Sub
LeeExchangeRate_Error:
MsgBox "Error " & Err.number & " (" & Err.Description & ") in procedure
LeeExchangeRate of Module xmlhttp_etc"
End Sub
On Wed, Jun 27, 2012 at 10:15 AM, Gustav Brock <gustav at cactus.dk> wrote:
> Hi Jack
>
> No, ecb.int is indeed the official site of the European Central Bank.
> Lots of info can be pulled here as well. Notice for example the
> excellent graph here for the USD exchange rate:
>
>
> http://www.ecb.int/stats/exchange/eurofxref/html/eurofxref-graph-usd.e
> n.html
>
> and the little link mid-right: -> XML (SDMX-ML) data file for the
> selection of data you have done with the slider controls.
>
> The site I referred to below is one of many of the European Union
> which offer lots of statistical material.
> It has just been decided that a goal (over some time) is to make all
> public data of the EU accessible for everyone to encourage businesses
> to extract whatever knowledge there may be hidden in this huge amount of
data.
>
> /gustav
>
>
> >>> jackandpat.d at gmail.com 27-06-12 14:19 >>>
> Darren,
> I got the code from UtterAccess and have not done much with it. I did
> find another site
> http://www.ecb.int/stats/eurofxref/eurofxref-daily.xml (European
> Central
> Bank) that was referenced in some materials and provides a list of
> currencies and the latest Euro relationship. However, I think Gustav's
> link is the "official site".
>
> I am interested in learning how to get material from the website using
vba.
> If anyone has
> good examples or tutorial for same or use of XMLHTTP , DOM etc with
> vba, please provide coordinates.
>
> Darren I thought you were familiar with the processes involved and
> just needed some base code to work with. Like many things, I find a
> few "procedures/routines" and save them for when I have more time(ha ha).
>
> Thanks Gustav for the site info.
> Jack
>
> On Wed, Jun 27, 2012 at 4:39 AM, Gustav Brock <gustav at cactus.dk> wrote:
>
> > Hi Darren et al
> >
> > Also note that the EU has this official site, InforEuro:
> >
> >
> >
> http://ec.europa.eu/budget/contracts_grants/info_contracts/inforeuro/i
> nforeuro_en.cfm
> >
> > with monthly exchange rates from the European Central Bank all the
> > way back from 1994 to the current month between Euro and just about
> > any currency.
> >
> > Data can be viewed on-line or downloaded in XML (though named as XLS
> > files) or CSV format.
> >
> > As rates are with six significant digits, you can create a
> > triangular conversion between any two currencies with a decent
precision.
> >
> > /gustav
> >
> >
> > >>> darren at activebilling.com.au 27-06-12 1:47 >>>
> > Hi Team,
> >
> > A couple of years ago I read an article that had some sample VBA
> > code on how to make contact with a webservice and retrieve currency
> > conversions.
> >
> > I wrote a nice little interface to it in Access and all was well
> > with the world.
> >
> > I have a need for this little app but can't, for the life of me,
> > find it anywhere.
> >
> > Anyone know of any code etc. that might help me re-build this thingy?
> >
> > Many thanks in advance
> >
> > Darren
>
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>
--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com