Excel 2003: Automatic Query Refresh

Factual Questions
1

I’m doing a bit of work with an existing Excel spreadsheet. There are some hardcoded paths scattered around the place, and my task is to go through it make the paths relative–or at the very least extract the paths to a single location that can be easily updated if need be. I’ve fixed up all the macros, and it’s mostly working fine, but there seems to be a hardcoded reference somewhere else that I simply cannot find.

As soon as I open the workbook, after the standard Enable/Disable Macros message, I get a dialog box saying “This workbook contains queries to external data that refresh automatically”, giving me the option to enable or disable automatic refresh. If I click Enable, Excel hangs for about 20 seconds, then says “Excel cannot find the text file to refresh this external data range.” I click OK and get another message saying “The following data range failed to refresh: IM41NW513_3. Continue to refresh all?”

I’ve looked everywhere, but I just can’t figure out what’s trying to pull in external data. I found an MS knowledge base article (http://support.microsoft.com/kb/248204) which says:

But I don’t have any experience with any of those three things, so I don’t know where to start. I’ve found menu items for creating new PivotTables and database & web queries, but nothing for existing ones. Any ideas?

ETA: I should mention that the sheet I’m working on is actually a template (.xlt). But the same thing happens whether I create a new sheet from the template or open the template directly, so I don’t think that makes a difference.

2

If the problem is in the form of an external data range you should be able to see the named range for that query.

Named ranges can be displayed in the “Name Box” in the toolbar area, or in Insert/Name/Define. You will see a name like “Query_From_something”. Delete it and your problem should go away. Or, if your curious you can right-click anywhere in the range and choose “Edit Query” or “Data Range Properties”.

3

Great, thanks for that! Turns out the workbook actually contains multiple named ranges with the same name but suffixed with _1, _2, _3, etc.

My guess is that the person who made the sheet tried multiple times to get it automatically importing from a set of text files, but eventually gave up and added a button to do it. But then he couldn’t figure out how to remove those named ranges, so he just learnt to cope with the message.

Thanks again!