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
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.