External Web Query

[Guest]

I have been doing External Web queries in Workbooks I designed for several
clients for over a year now. With a very high degree of success, we have had
few problems until recently. Now when we try to run a query in MSN Money,
after several queries have run, we get the following message:

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

Upon using the Edit Query option, we find that our check marks for the
specific Data Range(s) has been cleared. The Query seems to be intact but
the Range(s) are cleared and we must review all queries and make sure the
correct range(s) have been "re" checked.

To make matters even more confusing, it seems if we log off and turn off the
computer, later when we get on-line again, the queries will work again only
to then start having the same issue of the message coming up and having to
edit the query. No real pattern. The External Data Query will run a number
of times and then fail again.

Anyone have any ideas as to why suddenly a problem like this occurs after
running successfully for over a year?

Thanks for any suggestions.

 

[Don Guillett]

I have heard that many have had problems with MSN. Try switching to Yahoo.
I am having no problems with my files using Yahoo. Although I did have to
change urls recently.

 

[Guest]

I too have similar web queries and sometimes the same problem. If you look at
your web query macro, there is a line - .WebTables = "2,4,5,6" . This
results from your selections by ticking off the checkmarks and it can change
if the page is modified. My theory is that they put in an additional
advertisement or something and move the tables. If you redid the macro, the
line might read .WebtTables="2,5,7,8"

My solution has been that whenever the problem arises, I turn on "record new
macro" and do a new web query to that page. Then I go back into the VB editor
and see what the new WebTables are. Generally if I keep adding the new table
numbers to the old ones the problem will stabilize (but not go away). For the
example above, my macro would read .WebTables="2,4,5,6,7,8".

Another solution, if you can deal with it, is to set the .WebSelectionType =
xlAllTables and delete the .WebTables line. This downloads everything from
the page. This makes it very difficult for my program to pick out the data I
want but it will always download the data if you have a way to pick it out.

 

[Don Guillett]

If you download all tables you can use FIND in a macro to locate something
you know will be close. Then you can use OFFSET to pinpoint. Sometimes, I
have had to do this for clients that have xl97 against xl2002.