Most valuable professional
  • Excel Book Excerpt

Excel Web Queries Fail with Web 2.0

This page is an advertiser-supported excerpt of the book, Learn Excel 2007-2010 from MrExcel - 512 Excel Mysteries Solved. If you like this topic, please consider buying the entire e-book.

Web Queries Fail with Web 2.0

Problem: The previous editions of this book showed how to pull stock quotes from the web using Data, From Web. This worked great every five minutes for a couple years, but then it stopped working.

Strategy: The old web query technology was designed to get a table from a static HTML page. Now, with javascript and flash and newer technologies, the web query is often not seeing any tables on the page.

If you are trying to get data from a web page that still uses static tables, you might have some luck with web queries.

Here are the steps:

1. From the Data tab, choose from Web.

2. A browser opens in Excel. Navigate to the page. Let the page load completely. When the page finishes loading, look for small yellow arrow icons. If you see an arrow next to the table that you want, then you are in luck.

3. Click the yellow arrow. It will change to a green checkmark.

4. Click the Import button. Excel will ask where you want the data imported. Choose a location for the results.

5. Click OK. The dialog box will disappear. For a few seconds, you will see a strange value in cell A1, followed by “Getting Data.” In 1–10 seconds, the spreadsheet will redraw with a current version of the data from the Web page.

6. To refresh the data at any time, return to this worksheet and then click the Refresh All icon on the Data tab of the ribbon.


Figure 992 Choose the table to import.

Select a cell in the returned data and click the Properties icon on the Data tab. You can Refresh Data When Opening the File and/or to refresh the web query every N minutes.


Figure 993 Refresh data automatically every N minutes.

<-Previous Topic                    Next Topic->

For more resources for Microsoft Excel: