Excel:External data refresh problems

[Larft]

This is a really fun one.
I have an elaborate Excel 2003 formula calculation workbook that is saved as a template with multiple Vlookup tables that get their data from an Access database, I have them set to refresh on open. With the one generation of it everything works fine but I have set up a second version with some added sheets under a different template name, here is where the problem starts. If I open the template itself, not a new workbook based on it, the external data are refreshed properly and the underlying query remains intact, you can right click the sheet and the "! Refresh data" context item is still available and functioning. But, if I open a new workbook using the template, the first sheet of the 3 sheets that have the query for the external data no longer has the "! Refresh data" context item! The data are refreshed on creation of the workbook from the template but if you then save it and reopen it the query is gone and does not refresh. I've been trying to figure this one out for a while, I've tried saving the workbook as HTML and then getting into the file to try and see what's happening, I’m not a VB or HTML expert. I've tried moving the sheets around and can make one or more of them work or stop working depending on which order they are in, this appears as if Excel is losing track of the sheet aliases. I've also tried recording the code used to run the query using the macro recorder to see if I can set up a module to run it instead, the problem I run into is there are too many continuation lines in the resulting code for it to compile. It's as if the renaming of the template is the key but I can't see where that comes into play in the code.

Any one out there have any suggestions?

Thanks in advance for your help!

 

[SkipVought]

Hi,

Macro record editing each of the querytables: Data > Import external data > Edit -- File > Return data to Excel [red]ALSO, while in the QBE editor, open the SQL and COPY the code into a Notepad for later reference.[/red]

Post back with the code for ONE querytable edit, and the corresponding SQL that you copied to Notepad.

Skip,
[sub]
When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large[/sub]