VBA Join

Join.bas

Sub Join()
    On Error GoTo Error
    
    Const adOpenKeyset = 1
    Const adLockReadOnly = 1

    Dim con As Object
    Dim rs As Object
    
    Set con = CreateObject("ADODB.Connection")
    Set rs = CreateObject("ADODB.Recordset")
    
     With con
          .Provider = "Microsoft.ACE.OLEDB.12.0"
          .Properties("Extended Properties") = "Excel 12.0;HDR=YES;IMEX=1"
          .Open ThisWorkbook.FullName
     End With
     
     Dim searchString As String: searchString = "Kajima"
     
     rs.Open "SELECT * FROM ([SheetA$A1:C10] AS A LEFT JOIN [SheetB$A1:B10] AS B ON A.CountryID = B.CountryID) WHERE A.Name = """ & searchString & """ ORDER BY A.EmployeeID DESC ", _
        con, _
        adOpenKeyset, _
        adLockReadOnly
    
    'Set Header
    Dim i As Long, ws As Worksheet
    Set ws = Worksheets("Result")
    ws.Cells.Clear
    For i = 0 To rs.Fields.Count - 1
        ws.Cells(1, i + 1).Value = rs.Fields(i).Name
    Next
    
    'Set Record

    ws.Range("A2").CopyFromRecordset rs
    Exit Sub
    
Error:
    MsgBox Err.Description, vbCritical
End Sub

Private Function Inc(i As Integer) As Integer
    Inc = i + 1
End Function