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

About Me Search
Name:Alex Dybenko

Location:Moscow, Russia

Monday, October 15, 2007

Using HTTPXML to get data from the Web

This sample code shows how to use XMLHTTP library to send a request and then get a response  from web server. This code actually getting currency exchange rate from Central bank of Russia

Public Function GetRateCBR(dDate As Date, strCurCode As String) As String
Dim sUrlRequest, intTry As Integer, strResponse As String
Dim oResponse As Object 'MSXML2.XMLHTTP
'Sample usage:
' GetRateCBR(#10/12/2007#,"USD")
' GetRateCBR(#10/12/2007#,"Eur")
On Error GoTo GetRateCBR_Error

Set oResponse = CreateObject("MSXML2.DOMDocument")
'Send request
sUrlRequest = "" _
& Format(dDate, "") _
& "&date_req2=" & Format(dDate, "") _
& "&VAL_NM_RQ=" & "R0123" & IIf(strCurCode = "EUR", 9, 5)
' get response
intTry = 1
Do Until intTry > 10
Set oXMLHTTP = CreateObject("MSXML2.XMLHTTP")
oXMLHTTP.Open "GET", sUrlRequest, False
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
intTry = intTry + 1
If Not oXMLHTTP Is Nothing Then _
oXMLHTTP.abort: Set oXMLHTTP = Nothing
If intTry <= 10 Then
strResponse = Mid$(oResponse.Text, 3)
GetRateCBR = strResponse
End If
If Not oResponse Is Nothing Then _
oResponse.abort: Set oResponse = Nothing
On Error Resume Next
Exit Function

Select Case Err.Number
Case Else
MsgBox "No internet connection!" & vbCrLf & "Error " & Err.Number & " (" & Err.Description & ") in procedure GetRateCBR of Module alxmdlCurrencyRate"
Resume GetRateCBR_End
End Select

End Function


Technorati tags: , ,


Anonymous Tom Wickerath said...

Hi Alex,

The XML stuff you show for getting currency exchange rates is really cool! My wife is learning XML right now at her job.

Out of curiosity, I did a Debug.Print sUrlRequest
and quickly discovered that I could modify the function to return exchange
rates for a range of dates. Even more cool! For example:

This seems to return a result for each day the bank is open, during the two dates submitted.

Do you know how to get the "Record Date" value for each record?

So far, I've done the following modifications:

Public Function GetRateCBR _
(dDate1 As Date, _
dDate2 As Date, _
strCurCode As String) As String

'Added two declarations
Dim strResult() As String
Dim i As Integer

'Send request
'Modified: by substituting dDate1 & dDate2 in place of dDate

sUrlRequest = "" _
& Format(dDate1, "") _
& "&date_req2=" & Format(dDate2, "") _
& "&VAL_NM_RQ=" & "R0123" & IIf(strCurCode = "EUR", 9, 5)

If intTry <= 10 Then
strResponse = Mid$(oResponse.Text, 3)
GetRateCBR = strResponse

'***Added the next four lines***
strResult() = Split(strResponse, " 1 ")
For i = 0 To UBound(strResult) - 1
Debug.Print strResult(i)
Next i

End If

I'd like to be able to print the date with each value to the Immediate window.

Another question: What is this doing for EUR (the Euro?):

IIf(strCurCode = "EUR", 9, 5)

Thanks for posting. Really cool stuff!


PS. I noticed your entry for "....get lost like myself." {smile}

3:32 PM  
Blogger Alex Dybenko said...

Hi Tom,
thanks for comment and update! BTW, do you ever sleep? :-)

how to get the "Record Date" - i think best if you open returning XML in XMLDOM object and go through nodes. there are lot of samples in internet.

"What is this doing for EUR" -
VAL_NM_RQ parameter is to indicate currency to get exchange rate to rouble, so R01235 is usd/rub and R01239 eur/rub. But keep in mind that this server (central bank of russia) returns its own rates, what has sense in russia only.

4:19 PM  
Blogger grryf said...

Perfect. Exactly what I needed. Great website Alex!

2:16 PM  
Anonymous Anonymous said...

Hi Alex;
How can i get ip address that xml??

Please help..

Thanks a lot..

2:11 AM  
Blogger Alex Dybenko said...

just put this address in sUrlRequest and you will get XML back. So what is the problem?

10:24 AM  
Anonymous Anonymous said...

Hello again;

Sorry but i am newbie about xml. Please give me a sample code for get ip adress from

Thank you

6:15 PM  
Anonymous Anonymous said...

Ok no answer ;) I will try make it. But i want to know how get any string or text from web or xml. I want get any news (weather, currency rate etc.) For example;

(ip ip )
only or ;
(ip_long 1492562631 /ip_long)
only 1492562631
etc.. I cant convert your code for this..

and im sorry my poor English.

Thank you your interest..

4:02 PM  
Blogger Alex Dybenko said...

you can use XML DOM to extract values, search at for sample code

5:34 PM  
Anonymous Anonymous said...

Hi guys. I have strange problem. After I got the rate I'm running the script again but the script doesn't connect to the website, just returns previously calculated value. I tested it with other websites and have same results. The websites every time returns different value but the script returns same value. What is the problem?

1:30 PM  
Blogger Alex Dybenko said...

looks like a proxy server caching, try to add one more parameter to request with random value, for example time

3:45 PM  
Anonymous Anonymous said...

Looks like good idea! Thankyou!

4:18 PM  

Post a Comment

Links to this post:

Create a Link

<< Home