EXCEL to EXCEL Data Connection - This data source contains no visible tables warning

Question

1. In EXCEL I have set up a simple table (for testing purposes) that contains 2 columns and 2270 rows.

2. I am trying to set up a data connection to this EXCEL file from another EXCEL file.

3. These are the actions that I take in order to meet number 2.

4. I open the EXCEL file in which I wish to set up the new data connection and in this file I:-

Data tab

Get External Data group

Click on the drop down arrow to the right of:-

From Other Sources

 - then click on:-

From Microsoft Query

The:-

Choose Data Source

 - window opens.

5. In the:-

Choose Data Source

 - window the tab called:-

Databases

 - is selected. In that tab I click on:-

Excel Files*

 - and then I click on:-

OK

6. 2 windows now open.

The first is called:-

Select Workbook

 - and the second shows:-

Connecting to data source . . .

7. In the:-

Select Workbook

 - window I navigate to the file to which I wish to establish the data connection and I then click:-

OK

8. I now get a warning up:-

This data source contains no visible tables.

9. However, if I insect the data source, it correctly contains:-

Table1

MY QUESTION IS

How can I eliminate the warning that appears at number 8 above so that I can establish a data connection to the other EXCEL Workbook?

Thanks in advance for any replies.

Answer 1

I followed this procedure

https://support.office.com/en-za/article/Connect-data-in-another-workbook-to-your-workbook-3a557ddb-70f3-400b-b48c-0c86ce62b4f5

Event though it is for Excel 2013, it worked fine in Excel 2007.  Note that it indicates that the term Table refers to a worksheet in this approach.  I don't know if that is the same using MS Query.  

--

Regards,

Tom Ogilvy

Answer 2

Also check table options, and check off System Tables if its unchecked:

Answer 3

Select and Name the range of cells on the originating Excel Sheet you wish to import. For example name it "ALL".

On the destination Excel Sheet, look for the Microsoft Query>Excel Files* option; find the originating Excel Sheet to import; in your query parameter for "available tables and columns" select the table/column that is now visible and called "ALL"; click Finish; test to see if it works.

Let me know,

Al

Answer 4

Any other responses to my issue please?

I have tested this in 2007 and 2010 (on 2 different machines) and get the same problem in both.

Thanks in advance for any replies.

Answer 5

Can you recreate the issue with other files, is yes upload a sample of the files here and mention the exact steps to recreate the issue.