title: Combining two tables in Excel using VBA
body: <p>Using Excel VBA I would like to be able to combine two tables in excel with a common key. I have suggested ADODB as a method,but am open to any other more efficient/elegant methods. Please see below for a minimal example:</p>
  
  <p>I have the below to start with...</p>
  
  <p>Sheet1</p>
  
  <pre><code>    A     B       C
   1 type year1   year2
   2 aaa  100     110
   3 bbb  220     240
   4 ccc  304     200
   5 ddd  20      30
   6 eee  440     20
  </code></pre>
  
  <p>Sheet2</p>
  
  <pre><code>    A     B       C
   1 type year1   year2
   2 bbb  10      76
   3 ccc  44      39
   4 ddd  50      29
   5 eee  22      23
   6 fff  45      55
  </code></pre>
  
  <p>And would like to combine it so that I have the following as a result:</p>
  
  <p>Sheet3</p>
  
  <pre><code>    A     B       C       D       E
   1 type year1   year2   year1   year2
   2 aaa  100      110      0       0
   3 bbb  220      240      10      76
   4 ccc  304      200      44      39
   5 ddd  20       30       50      29
   6 eee  440      20       22      23
   7 fff  0        0        45      55
  </code></pre>
  
  <p>Have done a bit of googling and SQL type outer joins seems close but not sure how to implement it.</p>
  
  <p>Below is the code used to try and implement it so far...</p>
  
  <pre><code>Option Explicit
  
  
  
  Sub JoinTables()
  
   Dim cn As ADODB.Connection
   Set cn = New ADODB.Connection
  
  
   With cn
       .Provider = "Microsoft.Jet.OLEDB.4.0"
       .ConnectionString = "Data Source=" &amp; ThisWorkbook.FullName &amp; ";" &amp; _
           "Extended Properties=Excel 8.0;"
       .Open
   End With
  
   Dim rs As ADODB.Recordset
   Set rs = New ADODB.Recordset
  
   rs.Open "SELECT * FROM [Sheet1$] OUTER JOIN [Sheet2$] ON [Sheet1$].[type] = " &amp; _
       "[Sheet2$].[type]", cn
  
   With Worksheets("Sheet3")
       .Cells(2, 1).CopyFromRecordset rs
   End With
  
   rs.Close
   cn.Close
  
   End Sub
  </code></pre>