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

doug firr

Board Regular
Joined
Mar 28, 2011
Messages
140
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

Well-known Member
Joined
Dec 21, 2009
Messages
3,376
You shouldn't have to define a name for it.

Does this Table start in A1?
 

doug firr

Board Regular
Joined
Mar 28, 2011
Messages
140
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

Well-known Member
Joined
Dec 21, 2009
Messages
3,376
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

Board Regular
Joined
Mar 28, 2011
Messages
140
OK, that could be it, will update here. Cheers
 

Comfy

Well-known Member
Joined
Dec 21, 2009
Messages
3,376
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.
 

Forum statistics

Threads
1,082,314
Messages
5,364,475
Members
400,802
Latest member
RichBRich

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top