Query webpages using Excel

**Michael Sabin** · 06-04-2009

Hello..I am working for a corporate company where i have to work on Excel i usually required to import data from several sources for presenting data.

Could any one of you can make me know how to connect Excel to webpages that contains tables so that i can get extract the tabular data from there ?

**Raine** · 06-04-2009

Web queries are basically like having a browser built into Excel, which strips web pages of useless stuff and gives you access to the essential information. You can then use Excel formulas (like =A1/B2) to work directly with the data you've downloaded. And you don't have to know a lick about perl, cgi, php, javascript, and so on and so forth.

Follow this steps to query webpages using Excel:-

1. Click the Data menu > Import External Data > New Web Query

2. Enter the URL on the address bar then click go to display the page on the window.

3. Click the arrow next to the table that you want to import then click the import button.

4. Enter the worksheet where you want the data to appear then click OK

Thats it.. You’re done and should be able to see the tabular data appear on the sheet you specified above.

If you need to re-synch the data with the page contents, just click Data > Refresh Data

**ThoMas321** · 06-04-2009

Open up a text editor and copy the Google example. Save the file as GoogleSearch.iqy
Open up Excel and enter a search term in Cell B3
Open the web query file you just saved (GoogleSearch.iqy)
- Excel 2000: Go to Data --> Get External Data --> Run Saved Query...
- Excel XP: Go to Data --> Import External Data --> Import Data ...
When prompted for the search term, enter: =B3
If that doesn't work right off, you can edit your web query later, using the External Data Toolbar (Tools --> Customize ...)

Play around with the web query options and properties (using the External Data Toolbar). You will find that you need to select a cell inside of the web query before you can click on the toolbar buttons.

For the Google Search example, try selecting just the portion of the page that tells how many sites matched your search.

When you have the query looking just the way you want it, save the Excel web query (an icon or button for saving the web query is in the "Edit Query" window. When you save the new query, your ".iqy" file will include the options you have selected.