Get a FREE fully-working copy of Steganos Security Suite 7 (worth £49.99) when you sign up to the Practical PC Newsletter. How to Use Web Queries with Excel - Part 3
In this penultimate part of our mini series Ian Waugh suggests how to access all the data you've acquired from the Web...
In the first two parts of this series we looked at two ways of retrieving data from a web page. In this part we look at some shortcuts to preparing Web Queries and suggest ways in which you can access the data.
How refreshing
First of all, if you haven't done so already, open the External Data Toolbar from the View>Toolbars menu. You can dock it at the top or let it float.
The exclamation mark button - ! - and the second button from the right are used to refresh the data. The exclamation mark refreshes the currently-selected data while the other button refreshes all data.
Edit this
Select a cell which contains data retrieved from the Web and click on the first button in the External Data Toolbar. This brings up the Edit Web Query window which will be familiar. If you're creating lots of Queries, you can copy the URL select a new cell, select Data>Get External Data>New Web Query, paste in the URL and then make any changes necessary to get different data or data for another company.
You can also save the query from here which is useful should you want to run the same query in a different spreadsheet.
The second button on the Toolbar will also bring up a familiar window where you can adjust the data's properties.
Spaced out
If you experimented with the Web Queries from the previous you'll have noticed a strange thing. When you retrieve a single item of data, it's common for the the cell below it or to the side, although empty, to also a part of the data retrieval. You can see this by moving the cursor over the cells around the data cell.
When a cell is "attached" to the retrieval, the first two icons on the External Data Toolbar will become available.
Also, when retrieving a complete table as we did in Part 1, you'll undoubtedly find "empty" cells that are part of the table. There may also be items of data that you don't want but they come with the package as it were.
The problem with all this is that it makes it difficult to format the data - you simply can't make a good job of it if there are spurious empty cells on the sheet or if there is data there that you don't really want.
Between the sheets
The solution is to load the data into one sheet but create your layout in another sheet.
To do this you need to reference cells across sheets and this is easy to do. To reference a cell in the same sheet you simply type its name - i.e. A1, B7 and so on. To reference a cell in another sheet, you include the name of the sheet.
Let's say you've called the sheet containing the web data "Data". To reference its A1 cell in another sheet you'd use:
Data!A1
So, using this system you can pick and choose the items of data you want to use in your spreadsheet and create a customised set of tables or charts to display the data exactly as you want to see it.
In the final part we see how to construct a button to update the data whenever you click on it and also how to assign a URL to a button so you can quickly open a web site if you want to see the data in more detail.
