Web query with QueryTables

**fredoman** · 05-30-2009, 07:25 AM

Hello,
I am having problems getting to work a web query in a VBA macro. The goal is simply to get a value retrieved from a web query into an excel cell.

The below code works well with standard html pages (like replacing the url with www.google.com). However, the page I want to retreive (a query at geonames.org that retreives an altitude value for given lattitude/longitude values) does not contain any HTML headers, but just a number in plain text. In this case the below query does not returns anything. I tried different formating options for the query but up to nownothing works. Any idea how to solve this ? Thanks in advance !

Please Login or Register  to view this content.

**Chippy** · 06-02-2009, 03:35 PM

Try using IE automation and the HTML DOM, like this:

Please Login or Register  to view this content.

**fredoman** · 06-03-2009, 06:15 PM

Thanks a lot, your code definitive works. I hope it's reasonably fast since I will have typically 200-1000 lookups to do, maybe I will have to play around a bit. In any case I have a good starting point now.

**Chippy** · 06-03-2009, 07:42 PM

Yes, it should be reasonably fast. For multiple lookups, put the CreateObject and .Quit outside any loop, so that the IE object is created and destroyed only once. For faster performance you could try the WinHttpRequest object instead of InternetExplorer because IE is an unnecessary overhead for what you're doing (getting data from a web page with no need for the visual element that IE can provide).