[AccessD] pull data from web page

Hi Drew

Thanks for this example, works great!
A note related to internationalization. This line needs a mod like this:

strURL = "http://www.xe.com/ucc/convert.cgi?From=" & strOriginal & "&To=" & strFinal & "&Amount=" & Str(intValue)

Always use Str() to convert decimals to SQL strings and the like.

/gustav

>>> DWUTKA at marlow.com 13-12-2006 19:46 >>>
I put this into a sample app:  http://www.marlow.com/CurrencyChange.zip 

(pulled the currency codes off the site).

Drew

-----Original Message-----
From: Drew Wutka 
Sent: Wednesday, December 13, 2006 11:42 AM
To: accessd at databaseadvisors.com 
Subject: Re: [AccessD] pull data from web page

Option Explicit
Private Declare Function URLDownloadToFile Lib "urlmon" Alias
"URLDownloadToFileA" _
        (ByVal pCaller As Long, ByVal szURL As String, ByVal szFileName As
String, _
        ByVal dwReserved As Long, ByVal lpfnCB As Long) As Long
Function CurrencyConversion(strOriginal As String, strFinal As String,
intValue As Currency) As String
Dim strPath As String
Dim strURL As String
Dim strSearch As String
Dim f As Long
Dim strData As String
Dim strFirstData As String
Dim strEndData As String
strSearch = "<span style=""font-size:14pt; font-weight:bold;"">"
strPath = "C:\CurrencyTestData.tmp"
If Dir(strPath) <> "" Then Kill strPath
strURL = "http://www.xe.com/ucc/convert.cgi?From=" & strOriginal & "&To=" &
strFinal & "&Amount=" & intValue
URLDownloadToFile 0, strURL, strPath, 0, 0
f = FreeFile
Open strPath For Binary Access Read As f
strData = Space(LOF(f))
Get f, , strData
Close f
Kill strPath
strData = Mid(strData, InStr(1, strData, strSearch, vbTextCompare) +
Len(strSearch))
strFirstData = Left(strData, InStr(1, strData, "</span>") - 1)
strData = Mid(strData, InStr(1, strData, strSearch, vbTextCompare) +
Len(strSearch))
strEndData = Left(strData, InStr(1, strData, "</span>") - 1)
CurrencyConversion = strFirstData & " = " & strEndData
End Function

An example to run: 
MsgBox CurrencyConversion("USD", "NZD", 4.5)

Obviously you could change it so the function just returns the value of
strEndData, which would be the 'converted' value.  As far as the values of
"USD" and "NZD", don't have the time to pull those out of the web page for
ya, though it's not that hard to do.

Drew

-----Original Message-----
From: JWColby [mailto:jwcolby at colbyconsulting.com] 
Sent: Wednesday, December 13, 2006 7:57 AM
To: 'Access Developers discussion and problem solving'; VBA
Subject: [AccessD] pull data from web page

Guys,
 
I need to pull an exchange rate from a web page programmatically.  The web
page is:
 
http://www.xe.com/ucc/convert.cgi 
 
And I need to feed in the data and pull the results out of the page.
 
Is this possible?
 
If not, does anyone know of an exchange rate calculator that has live data
that is usable from inside of vba?
 
John W. Colby
Colby Consulting
www.ColbyConsulting.com