Категория: In English

I have an Excel workbook which calculates money amounts in multiple currencies, and I thought that it would be very nice to make it automatically get the newest exchange rates over the Internet.

I have always been using Google as my favorite currency conversion tool. You can try that yourself – for example, if you insert „100 GBP in USD“, it will show you the value of $100 in pounds sterling.

And so I was happy to learn that the XMLHTTP ActiveX control, which is very popular among web developers, can be accessed from Excel’s VBA macros too. That control is basically used to retrieve data from a HTTP server.

So it actually proved very simple to code an Excel macro that would connect to Google, get currency exchange rate data, parse it and put it in a cell.

Here’s the function that does the actual job:

Function GetCurrencyRate(Currency_1 As String, Currency_2 As String)
    Dim XMLhttp: Set XMLhttp = CreateObject("microsoft.xmlhttp")
    url$ = "http://www.google.com/search?q=1+" + Currency_1 + "+in+" + Currency_2

    XMLhttp.Open "GET", url$, False
    XMLhttp.send ""

    Result$ = XMLhttp.responsetext

    CBegin% = InStr(Result$, "<font size=+1><b>") + 17
    Result$ = Mid$(Result$, CBegin%)

    CBegin% = InStr(Result$, " = ") + 3
    Result$ = Mid$(Result$, CBegin%)

    GetCurrencyRate = Val(Result$)
End Function

As long as Google keeps the same page design, this function will work perfectly. Now any macro can call the currency exchange rate retrieval function and use the results. Here is the solution I chose for my case – an Auto_Open macro, which would automatically fire up when the workbook is opened, get the US Dollar / Euro exchange rate and put it in the F9 cell on Sheet 1:

Sub Auto_Open()
    R = GetCurrencyRate("USD", "EUR")
    Sheets("Sheet1").Range("F9").Formula = Str$(R)
End Sub