ALL ABOUT OFFICE

EXCEL

HOME

ACCESS

EXCEL

WORD

OUTLOOK

POWERPOINT

Importing Data from the World Wide Web

As well as bringing in data from text files and databases, Excel has a powerful facility to look up data from web sites and import the data to your spreadsheet.  Almost any data arranged in a table on a web  site can be imported.

To start the import, click on the Data drop-down menu and then choose Get External Data and then New Web Query.

Click on the Browse Web button and then use your web browser (e.g. Internet Explorer) to find the web page you want to import from.

When you have found the page, click on the Excel window behind your browser window and Excel will capture the address of the web page you visited.

In Section 2 of the Web Query window shown above you need to select which data you are going to import.  Most web pages contain several tables and so you may have to restrict which tables are imported by entering numbers representing where on the page the tables appear.  It is probably best to do this by trial and error as there is no helpful facility in either Excel 2000 or Internet Explorer to help you find the number for the table, versions of Excel from Excel XP onwards do provide more assistance with this.  You can also choose whether you want to apply all the HTML formatting of the web page to your spreadsheet or whether the simpler Rich Text formatting should be applied.

When you have completed this screen, click OK and you will be able to choose a location for the imported tables, either the current sheet or a new worksheet.

If you click on the Properties button you can set Excel to keep refreshing the data from the web site so it is kept up to date.

When you click OK, the data will be imported to your spreadsheet and formatted if you have chosen to apply formatting.  If you have set the spreadsheet to refresh you should see the program contacting the Internet at the set interval to retrieve the data again.  You should note that if you make any changes in the sheet containing the imported data they will be overwritten when the next refresh takes place.  If you want to carry out calculations using the imported data it is better to place them in a separate worksheet.

Click here to return to the Excel index.

webmaster@allaboutoffice.co.uk