Excel Tip of the Day: How to use Excel as a Relational Database Part II

Code can be found here JoinTablesII.xlsm
We are going now to join the Orders and Customrs Tables using MSQuery.
When I try to connect to Excel as datasource, my preferred way is to use a DSN connnectionless connection.
This is how you can to do it.

1) Rename Sheet3 to ReportCustomers
2) Data -> From Other Sources -> From MS Query

 3) Choose
4) The Create New Data Source window will show up
5) Name the DataSource NorthwindNoDSN
6) Choose the MS Excel Driver

7) Click Connect...
8) Select Workbook...
9) Select the current workbook from the list
9) Click OK 4 times


ops... you will get this error

10) Click Ok, Cancel, No
to return to MS Excel?

What did go wrong?
We have two nice table in our Excel workbook but MS Query cannot see them.
Les go to Name manager and have a look at it

 As you can see the two names are table in the Name Manager Dialog box. I know that to have MS Query see the tables I need to have Named Range (not Named Tables). So, let's try this out


1) Click New
2) Enter Customers as Name
3) And =TlbCustomers in the formula bar

Now we have a name range that point to the TblCustomers. Let's try if it works.

1) Data -> From Other Sources -> From MS Query
2) Select NorthwindNoDSN

You Still Get the same Error!!!!
We are just out of luck. This is something that Microsoft should fix in one of their future release!
Let try another solution.
It seems that pointing directly or indirectly to TblCustomers does not fix the problem.
So let create a Name that points to the directly to the range.

1) Formulas -> Name Manager -> Customers
2) Edit..
3) Then Make sure that the formulas looks like =Customres!$A$5:$F$34 and not like =TblCustomers[#All]

With this new name range try

1) Data -> From Other Sources -> From MS Query
2) Select NorthwindNoDSN

We Finally got it!!!
3) Select all the fields
4) Go to the End of the Wizard, Select Cell A5 in the ReportCustomers worksheet.

You have finally managed to query an Excel Table using MS Query.
As you can see from this screen shot, you have just created a ODBC Connectionless  connection to the JointTablesII excel file.

Rename the new Query to ReportCustomers going to the Name Manager as described above.

The Customers name range have these properties:

1) Will change automatically in size as we add new Customers
2) Will change automatically in size as we add column to the left in the Table
3) Will not work properly if we add a column to the right at the end of the table.

This is why I wished that Customers = TblCustomres[#All] would have worked.
So just keep an eye not to add columns to the end of the range.

The Orders name range have these properties:

1) Will change automatically in size as we add new Orders


So any time we change the columns from the underlying query that retrieve the order we will need to redefine it.

The fact the the two named range are not sensitive to new column in not a major annoyance.
Usually when a table is set up, the thing that is most likely to change is the row count not the column numbers.

In the next post you will see how to join the two tables and how to sort out some other minor annoyance.