Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Locating Query Errors in a Sea of Queries
Greetings! I have an Excel spreadsheet with lots of stock/option quote
data on a "quotes" sheet. I'm getting an "Invalid web query" error when I do a "refresh all", but I can't locate the offending query! Anybody know how to track it down? I've tried going through each query one by one, but do not get the error that way. I'm thinking there's an old (hidden?) query left around somewhere, but I can't find it. Thanks! Scott << |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Locating Query Errors in a Sea of Queries
sub ShowQueries()
Dim sh1 as Worksheet, sh as Worksheet Dim rw as Long, qt as QueryTable On Error Resume next Application.displayAlerts = False Worksheets("QueryList").Delete Application.DisplayAlerts = True On Error goto 0 worksheets.add After:=Worksheets(worksheets.count) Activesheet.Name = "QueryList" set sh1 = Activesheet rw = 0 for each sh in worksheets if sh.Name < "QueryList" then if sh.QueryTables.count 0 then rw = rw + 1 sh1.Cells(rw,1).Value = sh.name for each qt in sh.QueryTables rw = rw + 1 sh1.Cells(rw,2).value = qt.SQL sh1.Cells(rw,3).Value = qt.Connection sh1.Cells(rw,4).Value = qt.Destination.Address(0,0,xlA1,True) 'qt.Refresh Backgroundquery:=False rw = rw + 1 next end if end if Next End Sub Might be helpful. -- Regards, Tom Ogilvy "Scott" wrote in message oups.com... Greetings! I have an Excel spreadsheet with lots of stock/option quote data on a "quotes" sheet. I'm getting an "Invalid web query" error when I do a "refresh all", but I can't locate the offending query! Anybody know how to track it down? I've tried going through each query one by one, but do not get the error that way. I'm thinking there's an old (hidden?) query left around somewhere, but I can't find it. Thanks! Scott << |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
MS query--can it handle parameter queries from Access? | Excel Discussion (Misc queries) | |||
Locating Invalid Web Query Data | Excel Programming | |||
Web Query Errors | Excel Programming | |||
Running multiple queries in Microsoft Query in Excel | Excel Programming | |||
Font errors when performing multiple web queries | Excel Programming |