AccessBlog.net

News, links, downloads, tips and tricks on Microsoft Access and related

About Me Search
Alex
Name:Alex Dybenko

Location:Moscow, Russia

Thursday, December 07, 2006

How to retrieve data from web server

This can be done very easy using MSXML library, which is installed on any modern Windows OS. Below you can find a code to get exchange rates for USD from web server of Central Bank of Russia. You can easy modify code for your needs by changing sUrlRequest variable.

Function GetRateCBR(dDate As Date) As String
Dim sUrlRequest, intTry As Integer, _
strResponse As String
Dim oXMLHTTP As Object
Dim oResponse As Object

Set oResponse = CreateObject("MSXML2.DOMDocument")

'Build URL for request
sUrlRequest = _
"http://www.cbr.ru/scripts/XML_dynamic.asp?date_req1=" _
& Format(dDate, "dd.mm.yyyy") _
& "&date_req2=" & Format(dDate, "dd.mm.yyyy") _
& "&VAL_NM_RQ=" & "R01235"

'Try to get a response, 10 tries
intTry = 1
Do Until intTry > 10
Set oXMLHTTP = CreateObject("MSXML2.XMLHTTP")
oXMLHTTP.Open "GET", sUrlRequest, False
oXMLHTTP.send
If oXMLHTTP.Status = 200 Then
If oResponse.loadXML(oXMLHTTP.responseText) Then _
Exit Do
End If
If Not oXMLHTTP Is Nothing Then oXMLHTTP.abort: _
Set oXMLHTTP = Nothing
DoEvents
intTry = intTry + 1
Loop
If Not oXMLHTTP Is Nothing Then oXMLHTTP.abort: _
Set oXMLHTTP = Nothing
If intTry <= 10 Then
GetRateCBR = Mid$(oResponse.Text, 3)
End If
If Not oResponse Is Nothing Then oResponse.abort: _
Set oResponse = Nothing
End Function

0 Comments:

Post a Comment

<< Home