[AccessD] pull data from web page

JWColby jwcolby at colbyconsulting.com
Tue Dec 19 06:51:14 CST 2006


Gustav,

I tried to do this using a custom variable type (user defined type) which I
then filled with country code and rate, and store the custom type in the
collection.  Unfortunately I got a runtime error, something about the custom
type having to be declared public in a public module.  I think that it had
to be out in a DIFFERENT module.  At any rate I just said screw it, get it
done.  So I ended up with two collections instead of one.  Things never go
quite as planned and I needed to get on with real (paying) work.

John W. Colby
Colby Consulting
www.ColbyConsulting.com

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Gustav Brock
Sent: Tuesday, December 19, 2006 5:40 AM
To: accessd at databaseadvisors.com
Subject: Re: [AccessD] pull data from web page

Hi John

That's nice! It also works in Access 97.

I like your idea with the collection. As for the updating of this, you could
use Time or even Timer. That would allow you to use the function in a query
with a single update only while you in every other case would have an update
for each call.

Much appreciated.

/gustav

>>> jwcolby at colbyconsulting.com 13-12-2006 19:10 >>>
Gustav,

Thanks for pointing me to the XML feed.

The following function will load the url
http://www.ecb.int/stats/eurofxref/eurofxref-daily.xml into an XML document,
then burrow down into the xml document until it reaches the cube node, then
prints the attributes of that node (country code and rate) and finally,
returns the rate for the country passed in.  

I make no claim that this is the most efficient XML parsing code in the
world as I did the whole thing in a few hours, never having seen XML code
before, but it does work.

Copy and paste the following code into a module and save as basXMLRates.
WATCH FOR LINE WRAPS IN THE CODE AND FIX THEM.

Then from the debug window type:

?GetCurr()

You will get back the USD rate relative to the Euro.  Obviously you can
change the default to some other country

Type in ?GetCurr(,true) to print all the rates in the debug window as well
as the selected rate at the end

Type in ?GetCurr("JPY") to get the Japanese yen without the debug print.

To get the USD expressed in YEN type in 

?getcurr("usd") / getcurr("JPY")

Etc.

'*********************
'Start here
'*********************
Option Compare Database
Option Explicit
'
'This function uses static collections to hold the country codes and their
associated rates 'I do this so that if the function is called more than once
in a given day, the second and 'subsequent times don't have to go to the web
to get the rates '
'Obviously I am not handling rate updates at a given time of day.  That is
up to you.
'
Function GetCurr(Optional strCountryCode As String = "USD", Optional
blnPrint As Boolean = False) As Currency On Error GoTo Err_GetCurr Static
colRates As Collection Static colCountryCodes As Collection Static dte As
Date

Dim intCnt As Integer
Dim lstrCountryCode As String
Dim lcurRate As Currency
    If dte <> Date Then  'Check to see if this function was run today'
        dte = Date
        Set colRates = New Collection   'If not then initialize the
collections again
        Set colCountryCodes = New Collection
        TranslateXMLCurr colCountryCodes, colRates  'and go get the rates
for the day
    End If
    '
    'If a debug.print was requested
    '
    If blnPrint Then
        'iterate through the country code cilletion getting the country
codes
        For intCnt = 1 To colCountryCodes.Count
            lstrCountryCode = colCountryCodes.Item(intCnt)
            lcurRate = colRates.Item(intCnt)     'use that to get the rate
for that country
            Debug.Print lstrCountryCode & " : " & lcurRate    'print both in
a string to the debug window
        Next intCnt
    End If
    On Error Resume Next    'If the country code passed in is bogus, ignore
the error
    '
    'And pass back the requested rate
    '
    GetCurr = colRates(strCountryCode)
Exit_GetCurr:
On Error Resume Next
Exit Function
Err_GetCurr:
        MsgBox Err.Description, , "Error in Function basXML.GetCurr"
        Resume Exit_GetCurr
    Resume 0    '.FOR TROUBLESHOOTING
End Function
'
'This function looks up rates off of a web site and loads the country codes
and rates into two collections '
Function TranslateXMLCurr(colCountryCodes As Collection, colRates As
Collection) As Currency
On Error GoTo Err_TranslateXMLCurr
Dim fSuccess As Boolean
Dim oDoc As MSXML2.DOMDocument
Dim oRoot As MSXML2.IXMLDOMNode
Dim oCube As MSXML2.IXMLDOMNode
Dim oChild As MSXML2.IXMLDOMNode
Dim oChildren As MSXML2.IXMLDOMNodeList
Dim oAttributes As MSXML2.IXMLDOMNamedNodeMap


Dim lstrCountryCode As String
Dim lcurRate As Currency

    Set oDoc = New MSXML2.DOMDocument
    oDoc.async = False
    oDoc.validateOnParse = False
    fSuccess =
oDoc.Load("http://www.ecb.int/stats/eurofxref/eurofxref-daily.xml")
    '
    ' If anything went wrong, quit now.
    If Not fSuccess Then
      GoTo Exit_TranslateXMLCurr
    End If
    Set oRoot = oDoc.documentElement
    Set oCube = oRoot.ChildNodes(2).ChildNodes(0)
    Set oChildren = oCube.ChildNodes
    For Each oChild In oChildren
        Set oAttributes = oChild.Attributes
        With oAttributes
            lstrCountryCode = .Item(0).nodeTypedValue   'Get the country
code
            lcurRate = .Item(1).nodeTypedValue          'Get the current
rate
            colCountryCodes.Add lstrCountryCode
            colRates.Add lcurRate, lstrCountryCode
        End With
    Next oChild
        
    
Exit_TranslateXMLCurr:
Exit Function
Err_TranslateXMLCurr:
        
        MsgBox Err.Description, , "Error in Function
basXML.TranslateXMLCurr"
        Resume Exit_TranslateXMLCurr
    Resume 0    '.FOR TROUBLESHOOTING
End Function

'*********************
'End here
'*********************

Prints in the following manner:

USD : 1.3265
JPY : 155.34
CYP : 0.5781
CZK : 27.880
DKK : 7.4534
EEK : 15.6466
GBP : 0.67280
HUF : 253.48
LTL : 3.4528
LVL : 0.6973
MTL : 0.4293
PLN : 3.8008
SEK : 9.0407
SIT : 239.68
SKK : 34.775
CHF : 1.5951
ISK : 91.76
NOK : 8.1520
BGN : 1.9558
HRK : 7.3579
RON : 3.4247
RUB : 34.7980
TRY : 1.8919
AUD : 1.6811
CAD : 1.5273
CNY : 10.3819
HKD : 10.3094
IDR : 12031.36
KRW : 1223.76
MYR : 4.7018
NZD : 1.9172
PHP : 65.536
SGD : 2.0421
THB : 46.783
ZAR : 9.2630

Enjoy.

Perhaps we could put this code up on the web site as an extreme example of
bad programming practice in general as well as an intro of how to use the
XML object, and a currency converter (at least for mostly euro currencies).
I found it fascinating that given a XML feed, I could just suck it in and
go.  Of course the "going" was a tough slog never having seen the xml object
before...

John W. Colby
Colby Consulting
www.ColbyConsulting.com 

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Gustav Brock
Sent: Wednesday, December 13, 2006 9:37 AM
To: accessd at databaseadvisors.com
Subject: Re: [AccessD] pull data from web page

Hi John

This page may be of more help (it's European ...):

  http://www.ecb.int/stats/exchange/eurofxref/html/index.en.html 

Base currency is Euro so will have to do a conversion for USD but that
should be easy for you.
Note the link for other pages with sources for XML etc.
XML here:

  http://www.ecb.int/stats/eurofxref/eurofxref-daily.xml 

If you find a nice method, please share.

/gustav
--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com




More information about the AccessD mailing list