[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
>