Excel 2007 : ms query problem - internal data

[noeladams]

If I use MS QUERY in Sheet2 to copy data from Sheet1 in the same xlsx file, data refresh works fine. If I alter data in Sheet1 and refresh, the changes appear in Sheet2. However when I save the file and re-open it, refresh no longer works giving "ODBC driver login fail" with can't open file (unknown). Same behaviour in Excel 2010 Vista and Windows 7.

Any ideas please?

Noel Adams (Excel novice).

I have solved this problem (I think). Queries are held in the workbook and in external query files (if you choose to save the query). The queries held in the workbook are causing the error message. To overcome the problem: Create your query and save the query from the Query wizard giving it a meaningful name. Then cancel the query wizard (before letting the query run). Now choose, data, existing connections, and choose the query you just saved. Let it run and save the workbook. When you re-enter, refresh will work OK. Using this technique you may have several sheets with queries based on any prior sheet. The only trap I noticed is that "refresh all" does not necessarily work through the sheets in the sequence that you wish it to work. I can't find documentation on the default refresh sequence throught the sheets. I had deleted and renamed some sheets which may have altered the default sequence (whatever it is).