Locating Query Errors in a Sea of Queries

[Scott]

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

 

[Tom Ogilvy]

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.