Questions

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

+
0 Votes
Locked

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

mityak
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.)
  • +
    0 Votes

    Oh.

    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?

    +
    0 Votes

    XLS

    mityak

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

    +
    0 Votes
    TobiF

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

    +
    0 Votes
    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

    +
    0 Votes
    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.

    +
    0 Votes
    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.

    +
    0 Votes
    TobiF

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

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

    +
    0 Votes
    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

  • +
    0 Votes

    Oh.

    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?

    +
    0 Votes

    XLS

    mityak

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

    +
    0 Votes
    TobiF

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

    +
    0 Votes
    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

    +
    0 Votes
    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.

    +
    0 Votes
    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.

    +
    0 Votes
    TobiF

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

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

    +
    0 Votes
    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