Question

Locked

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

By 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.)

This conversation is currently closed to new comments.

9 total posts (Page 1 of 1)  
| Thread display: Collapse - | Expand +

All Answers

Collapse -

Oh.

by TobiF In reply to Excel Web Query - I can't ...

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?

Collapse -

XLS

by mityak In reply to Oh.

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

Collapse -

Add-ins?

by TobiF In reply to Excel Web Query - I can't ...

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

Collapse -

Add-ins

by mityak In reply to Add-ins?

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

Collapse -

Unless

by TobiF In reply to Add-ins

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.

Collapse -

It seems unlikely

by mityak In reply to Unless

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.

Collapse -

Hidden sheets?

by TobiF In reply to Excel Web Query - I can't ...

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>

Collapse -

Find the query by locating its name

by patobrien.3g In reply to Excel Web Query - I can't ...

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

Back to Web Development Forum
9 total posts (Page 1 of 1)  

Software Forums