External Data Query, named table not available for query in copies

Baxter · Apr 16, 2004

In Excel 2000 I have extracted data from one workbook into another using the Data > Get External Data, Databse > Excel functionality. I established a query and the desired data appears.

I require multiple source/template workbooks from which I plan to run the queries, but when I copied the original source sheet and try to use the Data > Get External Data, Databse > Excel method I used previously I get an error indicating no table is defined.

The table range is named the same as the original file, but Excel just doesn't seem to acknowledge it is there. Newly created test sheets work as expected. I have tried to delete and recreate the range, add new table ranges etc. but Excel will not recognize any table in the copied file.

Any ideas?

Dick Kusleika · Apr 16, 2004

Baxter

How are you copying the source? Are you copying the range, the sheet, the
whole workbook?

--
Dick Kusleika
MVP - Excel
Excel Blog - Daily Dose of Excel
www.dicks-blog.com

Baxter said:
In Excel 2000 I have extracted data from one workbook into another using

the Data > Get External Data, Databse > Excel functionality. I established
a query and the desired data appears.

I require multiple source/template workbooks from which I plan to run the

queries, but when I copied the original source sheet and try to use the Data

Get External Data, Databse > Excel method I used previously I get an error

indicating no table is defined.

The table range is named the same as the original file, but Excel just

doesn't seem to acknowledge it is there. Newly created test sheets work as
expected. I have tried to delete and recreate the range, add new table
ranges etc. but Excel will not recognize any table in the copied file.

Baxter · Apr 16, 2004

I copied the entire Workbook, same name, different folder - copy and paste in Explorer. I also tried a Save As... to copy the "working" original to a new location. Neither method seemed to matter. (Also, there are no folders using the same name as the file, as I have seen that can be an issue

I also used tried a copy of the sheet containing the table I'm concerned with out to a new workbook - no luck there either

SOLUTION! Eventually, as I was composing this, I discovered that if I copy it to a location on the same network drive it will function properly. It must be related to some network configuration(???). The original source Workbook was on network A, and the Workbook with the query was on network B. I copied the source workbook to network B, the query workbook remained on B and it didn't work. Copy of the source on Network A, query on network B works.

I'm puzzled by it, but at least I know the file is not corrupted. I may pass the question on the the network administrators to try and resolve further.

Dick Kusleika · Apr 16, 2004

SOLUTION! Eventually, as I was composing this, I discovered that if I
copy it to a location on the same network drive it will function properly.
It must be related to some network configuration(???). The original source
Workbook was on network A, and the Workbook with the query was on network B.
I copied the source workbook to network B, the query workbook remained on B
and it didn't work. Copy of the source on Network A, query on network B
works.

I'm puzzled by it, but at least I know the file is not corrupted. I may

pass the question on the the network administrators to try and resolve
further.

That's curious. I'd be interested to know if you find out anything more.

Thanks