Web query returned no data - turn off annoying message pop ups!

[christian_spaceman]

Hi all,

I've googled this a few times, but can't appear to find a solution.

I've just set up a spreadsheet which imports stock information. This
uses a webquery, refresehd once a minute, which is added through vba
and pasted below (bit horrible as it was recorded).

The stock name is in stockCell.value

Every so often, an error message pops up with "This web query returned
with no data... blah blah blah". Everything stops until I respond with
"OK" to this. Given that I want the macro to run constantly without my
babysitting it, it would be very useful to be able to turn this off.

Does anyone know how to do it? All I can think of is
application.displayalerts = false. I've put this in workbook_open and
it makes no difference. Given tha the web queries update automatically
once a minute, I'm not sure where to tackle this in the vba

Many thanks in advance,

Chris


ith ThisWorkbook.Sheets(stockCell.Value).QueryTables.Add(Connection:=
_
"URL;http://uk.finance.yahoo.com/q?s=" &
stockCell.Value, Destination:=Range("A10"))
.Name = "q?s=" & stockCell.Value & "_1"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 1
.WebSelectionType = xlSpecifiedTables
.WebFormatting = xlWebFormattingNone
.WebTables = "9,13,15"
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.Refresh BackgroundQuery:=False
End With ' end of With
ThisWorkbook.Sheets(StockCell.Value).QueryTables.Add(Connection:= _

 

[christian_spaceman]

But where to put it - in the same function that sets up the web
queries? I'll give it a go, but I'm not entirely sure that it will
solve the problem, since its not a *vba* error thats causing the pop
up, but an error with the web query (which is refreshing itself,
outside of my vba).

Def worth a go though!

Cheers,

Chris

 

[FSt1]

hi
i have limited experience with web queries but considerable expeience with
database queries. so i am doing some guessing.
the code you posted....do you run this code each time you refresh. if so
then you are re-creating a new query each time the code runs. once a query is
established, after that all you need to do is refresh the data meaning you
don't need to run this code each time you want to refresh. in fact, it's not
refreshing....it's recreating a new query from scratch. i think(guess) that
may be the source of your problem.
right click the query table and click data range properties. all of the
setting you set up in the code should be there. if you have it set to refresh
every minute, then this should be automatic. no need to run the code again.
see this site for form details.
http://www.mrexcel.com/tip103.shtml

regards
FSt1

 

[Don Guillett]

It appears that you may be creating new queries. If desired, send your
workbook to me for a look. You may want to go here and download a free file
of mine to download. 200 stocks with one mouse click.
goto
(e-mail address removed)
and look in the files section. There are several free files.
I have a couple of free files under author donalb36

 

[christian_spaceman]

Thanks for your replies guys. I don't think I was recreating the web
query multiple times - as that code I posted was only run once (at the
very beginning of the spreadsheet), and then set to update itself
without any further vba running. It was running into problems with a
network connection to yahoo I imagine (causing the annoying pop
up...).

I've now changed it, so that refreshperiod is set to zero (so no
automatic refreshing). I've then got another function which refreshes
all the querys in the workbook, and application.ontime's itself for
one minute in the futre - so the effect is the same. The difference
being that I can use on error resume next as suggested above. This
appears, so far, to be doing the trick.

Don - I'll pbly end up using your workbook, but I'm just messing about
at the minute for a bit of fun, and as I may use this workbook for
real in the future, I'd like to at least have a crack at writing it
myself

Thanks again,

Chris

 

[Don Guillett]

As I said, send your workbook if you like

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)
Thanks for your replies guys. I don't think I was recreating the web
query multiple times - as that code I posted was only run once (at the
very beginning of the spreadsheet), and then set to update itself
without any further vba running. It was running into problems with a
network connection to yahoo I imagine (causing the annoying pop
up...).

I've now changed it, so that refreshperiod is set to zero (so no
automatic refreshing). I've then got another function which refreshes
all the querys in the workbook, and application.ontime's itself for
one minute in the futre - so the effect is the same. The difference
being that I can use on error resume next as suggested above. This
appears, so far, to be doing the trick.

Don - I'll pbly end up using your workbook, but I'm just messing about
at the minute for a bit of fun, and as I may use this workbook for
real in the future, I'd like to at least have a crack at writing it
myself

Thanks again,

Chris