In 2003, I was able to setup a query to another spreadsheet using Microsoft Query. Trying to do the same thing in Excel 2007 I have three problems/questions.
The sample spreadsheet (Testsource1.xslm) has a single table and a single range that is defined as the same area as the table. Using a blank worksheet:
1. I start by using Data->From Other sources -> From Microsoft Query. When I select the source spreadsheet using Microsoft Query I get an error message "ODBC Excel Driver Login Failed" - "External Table is not in the expected format". If I have the source spreadsheet open when setting up the query, this doesn't happen? This was not a constraint in 2003. But I think it leads to other errors that occur later in this process.
2. When I did this under 2003, I got a wizard box "Choose Columns" which would list any named ranges in them to choose from. In 2007 I don't see either the table or the named range in the available tables and columns box. If I select options and pick all four check boxes, then I see something related to each worksheet name. I must have "system tables" and "tables" selected to see these.
3. If I select one of the tables related to Sheet1, I can setup the query. But when I try to edit the query, I get another ODBC Login Failed, this time with "Unrecognized database format. Seems to be related to number 1 above.
In summary I can't setup a query related to a named range or table without having the source file open. I then can't seem to edit the query if I base it on the system table (sheet1 for example). I expect that there is something that needs to be done with the way the source spreadsheet is setup.