An advanced way to import web data in Excel

As a web developer, I often have to create back-end databases that store a lot of useful information such as visits, user accounts, sales, and other useful information that is helpful when analyzing the performance of a website (especially if it’s an e-commerce site of some kind).

Some of my clients are business people who love spreadsheets. So I often have to provide an option to export the web servers’ databases in Microsoft Excel format.

When I have direct access to the server and its database, I can easily write code that exports those spreadsheets. But when dealing with data from third-party sources, sometimes I would have to “pull” the information and process it directly in Excel.

I was happy to learn that the XMLHTTP ActiveX control, which is very popular among web developers, can be accessed from VBA, the programming language used to automate Microsoft Office applications.

Typically, Excel’s web queries would be used to populate your worksheet with data from the web, but sometimes more advanced processing might be needed, so you could use ActiveX to grab the data off a web server.

Here is a very simple example snippet that processes information from Google’s currency conversion tool.

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$, "") + 35
    Result$ = Mid$(Result$, CBegin&)
    
    CBegin& = InStr(Result$, "") + 35
    Result$ = Mid$(Result$, CBegin&)

    GetCurrencyRate = Val(Result$)
End Function

Sub Auto_Open()
    'Put the value of a dollar in euros in cell F9 on Sheet1
    R = GetCurrencyRate("USD", "EUR")
    Sheets("Sheet1").Range("F9").Formula = Str$(R)
End Sub

Another example can be found here: http://dailydoseofexcel.com/archives/2005/10/13/reading-the-internet/ — as you can see, more advanced stuff, such as POST data and custom headers, is supported too.