Question Locked

Excel Web Query - I can't find the cell where it's located

0 helpful
9 replies
Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

I have inherited a excel file from a predecessor. Somewhere in the file there is a web query trying to pull data from a web page that no longer exists. As a result, I get an error message every time I open the file: "Unable to open http:_______. The internet site reports that the item you requested could not be found."

The problem is I can't find the file where the web query is, in order to correct the problem or delete it. I have already tried:

1. Find (by formulas, values, and comments)
2. Links
3. Macros

The file is large enough that even after some searching I can't find anything. And going to every cell in the file and then seeing if Data > Import External Data shows something just seems impossible.

Does anyone know how to search for web queries in an Excel file? Microsoft support was of no help. Thanks!

(We have Excel 2003 here.)

9 replies

by tobif

That's so much easier to give a shot when you have the file itself.

But, to start with, what file type is it trying to open over http?

by mityak

It's trying to open an Excel (.xls) file, actually.

by tobif

Have you any weird add-ins active?
Tools - Add ins.

by mityak

We have quite a few - the Analysis TookPak and Solver Add-in, which I think are Microsoft standard, and several developed in hour for other purposes. I don't think those should be the source of the problem, since I'm not getting this error with other files

by tobif

An add-in has a function, which only is called from this enormous file and that function, in turn, tries to access a url.

You could try to temporarily disable your local add-ins and see if this error disappears.

by mityak

Both because the file was developed prior to the add-ins and it just makes no sense that any of the add-ins would be doing a web query of any kind (let alone going to this particular website).

I have to say, the fact that this question is so difficult to answer/resolve is a little mind-boggling.

by tobif

You may have hidden or very hidden sheets.
Either browse the objects in the file in the VBA environment, or run this macro:

Sub SearchHiddenThings() With ActiveWorkbook For n = 1 To .Sheets.Count If .Sheets(n).Visible <> xlVisible Then MsgBox Sheets(n).Name & " " & .Sheets(n).Visible End If Next n End With End Sub

by patobrien.3g

paste the following sub into a code module, then (f5) run it. look for a name that looks like part of the query URL

Sub ShowSomeNamedRanges()

Dim n As Name

For Each n In Names
Debug.Print n.Name, n.RefersTo
Next

End Sub

This discussion is closed to new replies.