# Excel 2003: Automatic Query Refresh **URL:** \ **Category:** Factual Questions\ **Created:** [April 19, 2007, 11:14pm UTC](https://boards.straightdope.com/t/excel-2003-automatic-query-refresh/400857 "2007-04-19T23:14:42Z")\ **Posts on this page:** 3\ **Page:** 1
**Author:** ![Mbossa](https://avatars.discourse-cdn.com/v4/letter/m/73ab20/32.png) [@Mbossa](https://boards.straightdope.com/u/Mbossa)\ **Post date:** [April 19, 2007, 11:14pm UTC](https://boards.straightdope.com/t/excel-2003-automatic-query-refresh/400857/1 "2007-04-19T23:14:42Z")
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](http://support.microsoft.com/kb/248204)) which says: > [@](#): > > This message occurs when the following conditions are true: > [ul] > [li]Your workbook contains at least one PivotTable, database query, or Web query.[/li][li]The Refresh data on file open option in the External Data Range Properties dialog box is checked for at least one of the PivotTables, database queries, or Web queries in your workbook.[/li][li]Hi **Opal**![/li][/ul] 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. ---
**Author:** ![K364](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/k364/32/5_2.png) [@K364](https://boards.straightdope.com/u/K364)\ **Post date:** [April 20, 2007, 3:04am UTC](https://boards.straightdope.com/t/excel-2003-automatic-query-refresh/400857/2 "2007-04-20T03:04:24Z")
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”. ---
**Author:** ![Mbossa](https://avatars.discourse-cdn.com/v4/letter/m/73ab20/32.png) [@Mbossa](https://boards.straightdope.com/u/Mbossa)\ **Post date:** [April 20, 2007, 3:50am UTC](https://boards.straightdope.com/t/excel-2003-automatic-query-refresh/400857/3 "2007-04-20T03:50:38Z")
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!