VBA Web Query Looping multiple outcomes

So I have a web query that I would like to loop to a specified range. The catch is there are two possible outcomes. There is an outcome (data) and an outcome that is useless (no such page is found, etc). I'm kind of stuck trying to get this thing to loop. I would like to reference a range (contains links for input), and input all the data from the range, and based on the outcome, input the useful webpage data into the same spreadsheet.

Here is where I'm starting out.

Please Login or Register  to view this content.

Any help is greatly appreciated.

-huey

Try something like this:

Please Login or Register  to view this content.

The code above only handles the outcome when there is data retrieved from the web page: it assumes that if A1 on Sheet3 (the web query sheet) contains the text "Location Information" data then is available and is therefore copied. This assumption might be wrong.

If you provide some example URLs for a 'useless' outcome (e.g. no such page found, you say), I can modify the code to handle this too. For such cases, if the web query fails (errors out), then an On Error handler would be needed, otherwise if it retrieves data then a simple 'If ... Then' test would be used.

Note - for my testing, Sheet2 A1:A4 contain the same full URL:

Please Login or Register  to view this content.

I'm sorry for not being accurate in my description. The page actually says "Location with key "xxxxx" not found." Is there a way to advert the error through an if statement, or is it just easier to create an error handler?

Example:

http://store.publix.com/publix/cgi/s...pid=us&region=
http://store.publix.com/publix/cgi/s...pid=us&region=

Since this is going to be run on about 1500 cells, what lines should I use to avoid problems with timeouts?

Hello huey,

Welcome to the Forum!

To make your posts easier to read, copy, and edit please wrap your code. I did it for you this time. Here is how you can do it next time.

How to wrap your Code
1. Select all your code using the mouse.
2. Click on the # icon on the toolbar in the Message window. This will automatically wrap the text you selected with the proper Code tags to create a Code Window in your post.
3. To do it manually...
Use the Bulletin Board Code Tags
[code]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/code] at the end of the last line.


To learn more about BB codes used in this forum, just click on the link below...

Bulletin Board Codes Tags

The site has a limit on the # of queries per minute.

Originally Posted by Chippy

Try this code, which handles 'good' and 'bad' data outcomes. Put the code in a new workbook in a sheet module or a standard module (it should work in either) and run the Test macro.

Please Login or Register  to view this content.

The data input/output (location of URLs, columns, data copied etc.) might not be to your requirements, however it should be easy for you to modify.

What's interesting is that the query is producing an error in every cell on every request, even though there is "good" data from the page.