Darren
darren at activebilling.com.au
Tue Jun 26 20:10:32 CDT 2012
It works a treat - Genius stuff Jack
Now - How would I pass a Currency Value of Say One Canadian Dollar to see
what this equates to in British pounds?
I want to build a 4 field interface
2 drop down lists (From currency and To Currency)
The amount to convert (Default = 1)
And a Result field
Again many thanks in advance
Darren
-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of jack drawbridge
Sent: Wednesday, 27 June 2012 10:24 AM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] Exchange Rate - Currency Converter thingy
Darren:
I have this code in a module as a reference (from Utteraccess) - perhaps it
will help.
Jack
'---------------------------------------------------------------------------
------------
' Procedure : GetExchangeRates
' Author : Jack from UtterAccess doctor9
' Date : 21-11-2011
' Purpose : Interact with a web page
'---------------------------------------------------------------------------
------------
' Last Modified:
'
' Inputs: N/A
' Dependency: N/A
'--------------------------------------------------------------------------
'
Public Sub GetExchangeRates()
Dim ie As Object, strSource As String, strSQL As String
Dim strCountry As String, intMultiplier As Integer, sngRate As
Single
10 On Error GoTo GetExchangeRates_Error
20 Set ie = CreateObject("internetexplorer.application")
' No need to show the user the web page if we're just getting
values from it
30 ie.Visible = False
' Open the web page in our custom object
40 ie.Navigate "www.bnr.ro/nbrfxrates.xml"
' Wait until the web page is done loading
TryAgain:
50 While ie.Busy
60 DoEvents
70 Wend
' Get the outerHTML, which includes the XML on the web page
80 On Error GoTo TryAgain
90 strSource = ie.Document.body.outerHTML
100 On Error GoTo 0
' As long as there's another rate to read, keep looping
110 While InStr(1, strSource, "Rate</SPAN><SPAN class=t> currency") >
0
' Strip the front of the html text up to the country's initials
120 strSource = Mid(strSource, InStr(1, strSource, _
"Rate</SPAN><SPAN class=t>
currency") + 67)
' Assign the country initials to strCountry
130 strCountry = Left(strSource, InStr(1, strSource, "<") - 1)
' Strip away the country initials
140 strSource = Mid(strSource, InStr(1, strSource, "<") + 1)
' If there's a multiplier, extract it, otherwise skip it
150 If InStr(1, strSource, "multiplier") < 50 And _
InStr(1, strSource, "multiplier") > 0 Then
' Strip up through the word "multiplier"
160 strSource = Mid(strSource, InStr(1, strSource,
"multiplier") + 10)
' Then strip up through the first BOLD markup
170 strSource = Mid(strSource, InStr(1, strSource, "<B>") + 3)
' Assign the multiplier value to intMultiplier
180 intMultiplier = CInt(Left(strSource, InStr(1, strSource,
"<") - 1))
190 Else
' Default multiplier if there isn't one
200 intMultiplier = 1
210 End If
' Strip away the leading characters before the exchange rate
220 strSource = Mid(strSource, InStr(1, strSource, "class=tx") +
9)
' Assign the exchange rate to sngRate
230 sngRate = CSng(Mid(strSource, 1, InStr(1, strSource, "<") -
1))
' Create an Append Query using our three found values, and run
it
240 strSQL = "INSERT INTO tblExchangeRates " & _
"( strCountryInitials, intMultiplier, sngRate ) " & _
"VALUES ('" & strCountry & "'," & _
intMultiplier & ", " & sngRate & ");"
'** Adjusted Jed
'''''' CurrentDb.Execute strSQL, dbFailOnError
' Get Canadian Dollar
'If strCountry = "CAD" Then
Debug.Print strCountry & " " & intMultiplier & " " &
sngRate
' Else
' End If
250 Wend
' Remove the Internet Explorer object from memory
260 Set ie = Nothing
270 On Error GoTo 0
280 Exit Sub
GetExchangeRates_Error:
290 MsgBox "Error " & Err.number & " (" & Err.Description & ") in
procedure GetExchangeRates of Module Module2"
End Sub
On Tue, Jun 26, 2012 at 7:47 PM, Darren <darren at activebilling.com.au> wrote:
> 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