There are two ways you can feed live data from a web page into Excel. You can make menu and dialogue box selections in Excel or can perform more sophisticated operations using a web query file.
You can capture data which is held in a table or as preformatted text (inside the HTML <table> or <pre> tags).
You can easily import a table of data from a web page into Excel, and regularly update the table with live data.
This method is also useful for capturing static data from a web site. The data will be reliably imported into correct columns and rows in Excel. If you merely copy and paste data from a web page to Excel, you sometimes find the entire row of data is in one cell. (Using the Paste Special command will often also achieve this, but not always.)
For more flexibility in importing data use a web query file. This is a text file with the extension .iqy. Its contents looks like this:
http://weather.msn.com/local.aspx?wealocations=wc:UKXX0092 Selection=2 Formatting=None
To use this text file,
Other selection options are available: Selection=AllTables, Selection=EntirePage and Selection="name of table". To find the name of a table you will have to look at the source code of the page. For more than one table, separate the table names or numbers with commas.
Other formatting options are available: Formatting=None returns plain text without any formatting. You could also use formatting=All, to copy all the fonts and hyperlinks in the web page to the Excel sheet. Finally you could use Formatting=RTF, to keep most of the formatting put not the hyperlinks.
Using a query file gives you great flexibility. For instance, you can code the file so Excel will ask you which data items you want.
Excel calls this using dynamic parameters. As an example I'll use a Google search for "Microlink Measurement".
If you searched on Google for "Microlink Measurement", the
URL of the search results might look like this
http://www.google.co.uk/search?q=microlink+measurement
If you used this as your Web Query URL, the search
results for "Microlink Measurement" would be placed in
your spreadsheet.
However, if you use a line like this as your Web Query
http://www.google.com/search?q=["q","Enter the Search Term:"]
you will be asked for the search term Google is to use.
In Excel XP, you can also nominate a cell in the spreadsheet to hold the query term. Whenever the contents of this cell change, the data will be updated in Excel.
To do this:
Using a query file in this way provides an easy and quick way to get live data from the web into your spreadsheets. You can choose how often to refresh the data, or simply to keep it as it is.
More Excel Tips are on the Windmill Software site.