Excel VBA data connection code to join two worksheets in a SQL query.

Assume we have an Excel workbook with a Customer Item cross reference, and a second Excel workbook listing sales orders using our own item number.  If we want to see a report that shows sales orders by the customer item numbers we will need to create a query joining the two workbooks by our item number.

First let's add a function to create a worksheet and add a query.  We'll pass the SQL query and Table name to it in the arguments.  Note that the data source is self referencing.  The actual data location is defined in the SQL argument:

Function CreateQuery(SQL, TableName)
'Create a Query on a new worksheet
'We are going to "cheat" and use the active workbook for the DSN
'The SQL string defines the worksheets from which we load the actual data

    'delete any existing sheets with this name
    Application.DisplayAlerts = False
    On Error Resume Next
    Sheets(TableName).Delete
    Application.DisplayAlerts = True
    On Error GoTo 0
   
    'add a new sheet
    ActiveWorkbook.Worksheets.Add
    ActiveSheet.Name = TableName '<-Second function argument
   
    'Create the query in cell A1
    With ActiveSheet.ListObjects.Add(SourceType:=0, _
        Source:="ODBC;DSN=Excel Files;" & _
        "DBQ=" & ActiveWorkbook.FullName & ";" & _
        "DefaultDir=" & ActiveWorkbook.Path & ";" & _
        "DriverId=1046;MaxBufferSize=2048;PageTimeout=5;", _
        Destination:=Range("$A$1")).QueryTable
        .CommandText = SQL '<-First function argument
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = True
        .ListObject.DisplayName = TableName '<-Second function argument
        .Refresh BackgroundQuery:=False
    End With
    Range("A2").Select
End Function
 
Next we'll create a sub that will pass the SQL string and the Table name to the function we created.  Note that the SQL string in this sub uses a query that joins two separate spreadsheets:

Sub Main()
    Dim SQL As String
    Dim QueryResult As Variant
    Dim FirstPath As String
    Dim SecondPath As String

    'Get the files we want to use
    Application.FileDialog(msoFileDialogOpen).AllowMultiSelect = False
    Application.FileDialog(msoFileDialogOpen).Title = "Select the First Workbook"
    Application.FileDialog(msoFileDialogOpen).InitialFileName = "C:\YourPath\FirstWorkbook.xlsx"
    FirstPath = Application.FileDialog(msoFileDialogOpen).Show
    If FirstPath <> 0 Then
        'The FirstPath is the Customer Item cross reference workbook
        FirstPath = Application.FileDialog(msoFileDialogOpen).SelectedItems(1)
    Else
        'bail out if no file is selected
        Exit Sub
    End If
    Application.FileDialog(msoFileDialogOpen).Title = "Select the Second Workbook"
    Application.FileDialog(msoFileDialogOpen).InitialFileName = "C:\YourPath\SecondWorkbook.xlsx"
    SecondPath = Application.FileDialog(msoFileDialogOpen).Show
   
    If SecondPath <> 0 Then
        'The SecondPath is the sales order line items workbook
        SecondPath = Application.FileDialog(msoFileDialogOpen).SelectedItems(1)
    Else
        'bail out if no file is selected
        Exit Sub
    End If
       
    'Create the First Query Table
    SQL = "SELECT SECOND.Material, FIRST.CustItem, SECOND.Description, SECOND.Order, SECOND.Price, SECOND.Qty " & _
        "FROM `" & SecondPath & "`.`Sheet1$` SECOND " & _
        "LEFT JOIN `" & FirstPath & "`.`Sheet1$` FIRST " & _
        "ON SECOND.Material=FIRST.Material " & _
        "WHERE SECOND.Customer = 'AAA Wash Basins' " & _
        "ORDER BY FIRST.CustItem, SECOND.Material"
       
    'now we run our function to create the query
    QueryResult = CreateQuery(SQL, "CustomerOrders")
    'Add the total rows to the Table (optional)
    ActiveSheet.ListObjects("CustomerOrders").ShowTotals = True
    ActiveSheet.ListObjects("CustomerOrders").ListColumns("Order"). _
        TotalsCalculation = xlTotalsCalculationSum
    ActiveSheet.ListObjects("CustomerOrders").ListColumns("Qty").TotalsCalculation _
        = xlTotalsCalculationSum
   
End Sub