[AccessD] pull data from web page

This one is particularly valuable to me as it demonstrates how to pass the
parameters to the web page and get the value back.  I often have a need for
this technique.

Nice. 


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:22 AM
To: accessd at databaseadvisors.com
Subject: Re: [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 

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