Web Query Lessons

I’ve recently learned some things about Web Queries that I’d like to share with you. But first a little background. You create a Web Query in Excel 2007 by clicking From Web on the Data tab. This launches a browser within Excel through which you navigate to a web page and select a “table”. Tables on web pages can be a few different things. I think the common “tables” are html tables and html div tags.

Lesson 1: The web browser Excel uses is some variant of Internet Explorer. If you don’t use IE, and I don’t, then you haven’t bothered to set the home page. In that case, the home page will be msn.com and you might see errors like this:

Awesome. Why did I ever stop using IE? If I open IE8, set my home page to google.com (or anything else), then the errors go away. Bonus April Fools day tip: Find a co-worker who uses Firefox and set his IE home page to hawtness.com. It’s marginally NSFW, so it will be funny when he does a web query, but he won’t get fired (probably).

Lesson 2: If you try to refresh a Web Query for a web page that requires you to log in, it won’t work (sometimes). You’ll get

This Web query returned no data. To modify the query, click OK, click the name of the external data range in the name box on the formula bar, and then click Edit Query on the External Data toolbar.

I have a workbook with a couple dozen web queries all pointing to the same site. If I ‘edit’ one of the queries, Excel’s instance of IE remembers that I have logged in (cookies maybe? that aren’t shared with normal IE?) and I can refresh all the remaining queries without error. So I created a sheet near the beginning called ‘Credentials’ where I have a Web Query that brings in a small table near the top of the home page. I ‘edit’ that query to log in, but I don’t have to navigate around much to bring it in. Then I run my code which updates all the URLs and refreshes all the queries. The poor man’s IE automation, I guess.

Posted in Uncategorized

3 thoughts on “Web Query Lessons

  1. The tricky part is selecting the table. The yellow marks for table identification in IE is different than a table in Excel. HTML view of tables is not necessarily tables but css style sheet layouts. Danger Will Robinson.

  2. I once had a spreadsheet that scraped for webpage links and a countdown timer, and at the specified time followed the link and retrieved the next page. It was also from a website that required my login.

    I recall that I had to log in to the site in a regular IE window, then the web queries in Excel would work… until the cookie expired. So based on that, I would suggest that the cookies ARE shared with IE, or at least Excel has read only access to IE’s cookies…

    Ah the things one does to get some sleep when playing a 24/7(/52) browser game…

  3. I made a kind of web service to feed data from Excel to a DB. Works great. The problem I have
    with WebQuerys is that it doesn’t seems to recognize tables from pages that are designed
    with divs instead of table layouts… kind of a bummer.

Posting code? Use <pre> tags for VBA and <code> tags for inline.

Leave a Reply

Your email address will not be published.