Incorrect Warning Message, “This data source contains no visible tables.”

Question

1. In EXCEL 2007 on Windows 7 I have created a Data Connection between 2 EXCEL Workbooks.

2. I have now come round to set up a similar Data Connection between 2 EXCEL Workbooks but this time working in Microsoft EXCEL Professional Plus 2013 on Microsoft Windows 7 Professional.

3. (a) I repeat the steps that I took in the 2007 version to set up the Data Connection in the 2013 version.

These steps are:-

3. (b) I set up the first file:-

z:\data_connections_testing_jan_27_2015_source_of_data_connection.xlsx

In the above in Sheet1 I have Table1 comprising 4 columns and 472 rows.

This is the file to which I am going to create a Data Connection.

3. (c) I set up the second file:-

z:\data_connections_testing_jan_27_2015_destination_for_data_connection.xlsx

In this second file I:-

3. (d) Click in Sheet1 cell A1 then:-

Data tab

On the far left hand side click on the drop down arrow to the right of:-

Get External Data

 - then click on:-

From Other Sources

 - then click on:-

From Microsoft Query

The window called:-

Choose Data Source

 - opens.

3. (e) In the window called:-

Choose Data Source

 - in the tab called:-

Databases

 - I click on:-

Excel Files*

 - then click on:-

OK

The window called:-

Select Workbook

 - opens.

3. (f) In the window called:-

Select Workbook

 - in the field called:-

Database Name

 - I enter the name of the file to which I wish to establish a Data Connection

z:\data_connections_testing_jan_27_2015_source_of_data_connection.xlsx

 - and I then click:-

OK

4. EXCEL now responds with a warning:-

This data source contains no visible tables.

This warning is clearly wrong as step 3. (b) shows (it does contain a Table).

I don’t get this warning in my EXCEL 2007 on Windows 7 configuration.

How do I get round the warning at 4. above so that I can set up my Data Connection successfully in my Microsoft EXCEL Professional Plus 2013 on Microsoft Windows 7 Professional configuration?

Thanks in advance for any replies.

Answer 1

HI,

Thank you for choosing Microsoft Community, 

Follow the steps provided below to set up my Data Connection successfully using Excel.

1. OK once you get the error ‘This data source contains no visible tables’.
2. Query Wizard - Choose Columns dialog box, and then click Options.
3. Table Options dialog box, click to select the System Tables check box, and then highlight the sheet under ‘Available tables and columns’ and click on arrow to move it to ‘Columns in your query’.
4. Then follow the onscreen instructions.

For more information on the possible cause and the above steps refer the link below.

http://support.microsoft.com/kb/286891

Hope this helps, please do get back to us if you need more assistance.

Thank you.

Answer 2

HI,

Thank you for choosing Microsoft Community, 

Follow the steps provided below to set up my Data Connection successfully using Excel.

1. OK once you get the error ‘This data source contains no visible tables’.
2. Query Wizard - Choose Columns dialog box, and then click Options.
3. Table Options dialog box, click to select the System Tables check box, and then highlight the sheet under ‘Available tables and columns’ and click on arrow to move it to ‘Columns in your query’.
4. Then follow the onscreen instructions.

For more information on the possible cause and the above steps refer the link below.

http://support.microsoft.com/kb/286891

Hope this helps, please do get back to us if you need more assistance.

Thank you.

. . . that works spot on. Thanks!

Answer 3

HI,

Thank you for choosing Microsoft Community, 

Follow the steps provided below to set up my Data Connection successfully using Excel.

1. OK once you get the error ‘This data source contains no visible tables’.
2. Query Wizard - Choose Columns dialog box, and then click Options.
3. Table Options dialog box, click to select the System Tables check box, and then highlight the sheet under ‘Available tables and columns’ and click on arrow to move it to ‘Columns in your query’.
4. Then follow the onscreen instructions.

For more information on the possible cause and the above steps refer the link below.

http://support.microsoft.com/kb/286891

Hope this helps, please do get back to us if you need more assistance.

Thank you.

OK - thanks Samuel; I'll check it out over the next few days.