415 Systems - Technology for Office users

A very useful feature introduced in Excel 97 enables you to webscrape a site and import defined areas into a spreadsheet. There are a variety of situations where this could be useful:

- Utilizing real-time financial market data.
- Importing weather forecasts.
- Checking if your ads are appearing in a search engine.
- Viewing the latest headlines.

To import data from a webpage:

1. Click Data –> Get External Date –> From Web.
2. Enter the URL to scrape (eg. http://news.google.com/).
3. Click the yellow areas for the page divisions you wish to import.
4. Click ‘Options…” to specify how HTML formatting and PRE are handled.

After importing, right click anywhere in the imported block and click ‘Data Range Properties’ to specify how often the page should be refreshed (the minimum time is 1 minute). Click here for a spreadsheet that demonstrates the four sample uses above.

Unfortunately, there is no specific event that is fired when your web query refreshes. However, you can use the Worksheet_Change event and verify that the target range parameter matches the cell area for your web query (see the sample spreadsheet’s VBA module for details). Frequently this is required since there is usually some parsing required from the results.

If the source data of your query is dynamic (eg. you a looking up an area code driven by user-generated data), it is simple to use VBA to generate the web query results:

Dim sSearchTerm As String
sSearchTerm = Cells(2, 2) ‘ For example…

With ActiveSheet.QueryTables.Add(Connection:= _
“URL;http://www.google.com/search?hl=en&q=” & sSearchTerm, Destination:=Range(”$B$4″))
.WebSelectionType = xlEntirePage
.WebFormatting = xlWebFormattingNone
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.Refresh
.BackgroundQuery:=False
End With

Although web queries lack the full power available by using MSXML or WinSock to retrieve web data, for simple queries they provide a very rapid and reliable way to scrape a webpage.