| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Web query returned no data - turn off annoying message pop ups!
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.A dd(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.A dd(Connection:= _ |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Web query returned no data - turn off annoying message pop ups!
Try On Error Resume Next
Mike F "christian_spaceman" wrote in message ... 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.A dd(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.A dd(Connection:= _ |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Web query returned no data - turn off annoying message pop ups!
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 On Jul 11, 1:24*pm, "Mike Fogleman" wrote: Try On Error Resume Next Mike F"christian_spaceman" wrote in message ... 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.A dd(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.A dd(Connection:= _- Hide quoted text - - Show quoted text - |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Web query returned no data - turn off annoying message pop ups!
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 "christian_spaceman" wrote: 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.A dd(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.A dd(Connection:= _ |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Web query returned no data - turn off annoying message pop ups!
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 On Jul 11, 2:49*pm, "Don Guillett" wrote: 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 * * * * * * and look in the files section. There are several free files. * * * I have a couple of free files under author donalb36 -- Don Guillett Microsoft MVP Excel SalesAid Software "christian_spaceman" wrote in message ... 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.A dd(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.A dd(Connection:= _- Hide quoted text - - Show quoted text - |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Web query returned no data - turn off annoying message pop ups!
|
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Query returned more data than will fit on a worksheet | Excel Worksheet Functions | |||
| Transposing data as it is being returned from an query | Excel Programming | |||
| Query returned more data than will fit on a worksheet | Excel Discussion (Misc queries) | |||
| This query returned no data | Excel Worksheet Functions | |||
| The Web query returned no data | Excel Programming | |||
Linear Mode
