Shadow Zhang

Set up the Workbook

In this example, there are two tables -- Raw Materials and Packaging -- and each table is on a separate worksheet.

The Raw Materials table is on the sheet named Materials, and the Packaging table is on the sheet named Packaging.

The third sheet in the workbook is named Combined, and this is where the query results will be stored.

With Microsoft Query, you can create a list that combines each item in one table, with all the items in the other table -- a Cartesian join, also called a cross join. You can read more about the different join types on the Microsoft website: Description of the usage of joins in Microsoft Query

Open MS Query

To create the Cartesian (cross) join, you'll use MS Query.

1. On the Excel Ribbon, click the Data tab
2. In the Get External Data group, click From Other Sources, then click From Microsoft Query

3. In the Choose Data Source window, click on Excel Files*, and click OK

4. In the Select Workbook window, locate and select the current workbook, and click OK.

5. In the Query Wizard, if you don't see the sheet names listed, click Options, and add a check mark to System Tables

6. Click Materials$, and click the arrow to put the Raw Materials column in the query.
7. Click Packaging$, and click the arrow to put the Packaging column in the query

8. Click Cancel, to close the Query Wizard, and click Yes when prompted.

9. In Microsoft Query, double-click on Raw Materials in the Materials$ table, to add it to the query grid.
10. Then, double-click on Packaging, to add it to the query grid.

11. Click the Return Data button, to send the data to Excel.

Create a Worksheet Table

It might take a few seconds, but then the Import Data window will open.

1. In the Import Data window, select Table
2. Select the cell on the worksheet where you want to place the query results, and click OK.

3. A table is created, and shows all the items from each table, in all possible combinations.

Add Formulas to the Table

You can add formulas to the table, in a new column. The formulas will automatically adjust if the source tables are changed.

1. Type a new heading in cell C1 -- MatPack -- and press Enter
2. A new column is automatically included in the table.
3. In cell C2, type a formula to combine the text in columns A and B, with a space character between them:

   =[@[Raw Materials]] & " " & [@Packaging]

4. Then, copy the formula down to the last row of data in the table.

Update the Table

You can update the source tables, and then update the query results table, to show the revised data.

1. Add a new item to each of the source tables. In this example, Boxes was added to the Packaging list, and Cream was added to the Raw Materials list.

2. On the Excel Ribbon, click the Data tab, and click Refresh All.

3. The new items are shown in the updated query results table.

Manually Update the Workbook Name

If you change the workbook name, the query will need to be updated, before it will run. To manually update the query:

1. Right-click a cell in the results table, and click Refresh
2. When the Login Failed message appears, click OK

3. In the Select Workbook window, locate and select the new workbook, and click OK.

Update the Query Connection with VBA

If the file name or location will change frequently, you can use programming to automatically change the file location in the connection.

Paste the following code -- FixQueryConnection -- into a regular module in the workbook, and then run the code when the workbook opens, by adding a Workbook_Open event.

This code was tested in Excel 2010 (32-bit), and might need to be adjusted for other versions of Excel.

Sub FixQueryConnection()
'
Dim strFile As String
Dim strPath As String
Dim strQry As String
Dim strCmd As String
Dim strConn As String

strPath = ActiveWorkbook.Path & "\"
strFile = ActiveWorkbook.Name
strQry = "Query from Excel Files"
strCmd = "SELECT `Materials$`.`Raw Materials`, `Packaging$`.Packaging "
strCmd = strCmd & "FROM `Materials$` `Materials$`, `Packaging$` `Packaging$`"
strConn = "ODBC;DSN=Excel Files;DBQ=" & strPath & strFile
strConn = strConn & ";DefaultDir=" & strPath
strConn = strConn & ";DriverId=790;MaxBufferSize=2048;PageTimeout=5;"
'
    With ActiveWorkbook.Connections(strQry).ODBCConnection
        .BackgroundQuery = True
        .CommandText = strCmd
        .CommandType = xlCmdSql
        .Connection = strConn
        .RefreshOnFileOpen = False
        .SavePassword = False
        .SourceConnectionFile = ""
        .SourceDataFile = ""
        .ServerCredentialsMethod = xlCredentialsMethodIntegrated
        .AlwaysUseConnectionFile = False
    End With
    With ActiveWorkbook.Connections(strQry)
        .Name = strQry
        .Description = ""
    End With
    ActiveWorkbook.Connections(strQry).Refresh
    
    ActiveWorkbook.RefreshAll
End Sub
'====================================          

Put this code into the ThisWorkbook module:

Private Sub Workbook_Open()
  FixQueryConnection
End Sub
'====================================