Problem with external data web query

[Alex]

Hello

I have an Excel spreadsheet that obtains data from the
internet. It is updated daily.

In all there are 42 separate web queries on my
spreadsheet. They all download almost identical tables of
identical sixe (7C x 50R)

If I choose to update each morning by using the <Refresh
All> button on the External Data Toolbar then problems
occur. Most notably, some data are not downloaded. About
7 queries out of the 42 have faults.

If I update manually (i.e. go to each table in Excel and
right click and select <!Refresh> then the success rate
is much higher. Still, and more infuriatingly, sometimes
I have to select <!Refresh> 2 or 3 times before the table
updates.

Is there a way around this and have people found similar
problems? It seems that if Excel has to update all at
once it gets 'confused'.

Any ideas always wlecome.

Regards


Alex

 

[Frank Stone]

not sure about this. i've never had to refresh a query
from the web but you might writing a macro to refresh each
query one at a time;

range(query1).select
selection.querytable.refresh backgroundquery:=false


I haven't seen your data/file so i don't know if you could
put it all in a loop.
as to having to refresh more than once, not sure what to
do about that. sounds like a timeout problem.(guess)
Is it restricted to the same queries everyday or different
one each time.

 

[Guest]

Frank Stone

It is the same query every time, thought the data changes
on a dily basis.

For example, look at URL

http://finance.yahoo.com/q/hp?s=^SPX

Here you will see a data table. That is what I download.
Each day the top line changes as a new day of data is
added. e.g tomorrow will have 'Aug 4' as top row instead
of 'Aug 3'. So the query (URL doesn't change) but the
table changes by one row.

I have 42 queries the same as this, each for a different
market e.g. ^DJI, ^IXIC etc. All the downloaded tables
lie side by side in a single Excel worksheet.

I tried writing VBA to refresh each individually. I
acheived mixed results.

Does this help any?

Regards

Alex