Connect to an internal named range or table in excel for MSQuery

[doug firr]

Someone on this forum recently told me about MSQuery which I'm really existed about. He did say it was fiddly but I can;t event take it for a test drive!

I have a tab in my workbook called "DataBase" with all the data. I have selected the whole thing and given the range a name "DataBase". I have also tried turning it into a table "Table1".

But how do I open this feature to start using MSQuery? On the data tab in the ribbon there is an option under external data for "From MS Query" but when I navigate to the excel workbook I'm in I get an error message "The data source contains no visible tables".

Can someone hold my hand here to get me started? The range of cells "DataBase" which is also a table "Table1" has all the data I need if I could only query it I could take it from there I just can't get up n going.

How do I get my environment so that I can start querying "Table1" or "Database" named range?

Excel 2007.

 

[Comfy]

You shouldn't have to define a name for it.

Does this Table start in A1?

 

[doug firr]

Hi Comfy. Yes, it starts in A1 on a separate tab "Database" which has a named range starting in A1 called "Database" which is the same range as "Table1". How do I open MSQUery to start querying this range?

 

[Comfy]

I had this problem once but I can't remember the details.

If I remember correctly my issue was either due to blank rows in the worksheet or the file being .xlsm/containing macros, or something else.

 

[doug firr]

OK, that could be it, will update here. Cheers

 

[Comfy]

Hi Doug,

I came across the issue again and can confirm it is because the file is macro-enabled (.xlsm)

This post provided a solution: Using Excel Macro (.xlsm) Enabled Files in SSIS | Devin Knight

 

[Comfy]

Scrub that, that wouldn't allow me to use MS Query to join tables I then stumbled across the following.

Once you get the message, "This Data Source Contains No Visible Tables" click ok and you should get the "Query Wizard - Choose Columns" window.

Select option and tick System Tables, this should then display all the tables in the workbook.