How to join two excel worksheets based on a column as key using VBA

Question

I am not sure what approach to take but wondering can I join data from two excel sheets and populate in a 3rd sheet like how we join in SQL using VBA. Example data is below, I would like to join Sheet1 and Sheet2 by Emp_id and populate the result in Sheet3. I have googled a lot and tried whatever I know but nowhere I am close to get this, can someone throw some light on this or share an working example, that would be great. I am still trying and will post if I got a solution.

You could copy and paste sheet 2 into sheet three then use a vlookup. — Scott Craner
– Scott Craner, Commented Jun 4, 2016 at 1:45
Do you have Excel 2016? Then you'll find within the Data tab the Get & Transform area with the option to New Query ► Combine Queries ► Merge. This will (almost) automatically merge both tables for you with an automated background refresh if any of the underlying table changes. Otherwise, you can write an ADO query. For more on this read this or search for similar solutions on SO. — Ralph
– Ralph, Commented Jun 4, 2016 at 12:50
Thanks @Ralph, I will try the ADO method, I am using 2013 version and my sample data was too simple compare to actual, that's why I felt this may not be easily done using vlookup. I will try ADO and share the outcome. — Chito
– Chito, Commented Jun 5, 2016 at 3:47

Chito · Accepted Answer · 2016-06-06 18:00:22Z

1

Below works for mine, again below query is for the sample data. For my actual data, I had to get the sheet number to use sheet name (example [Sheet2$] is replaced with [Sheet" & Sheets("Source").Index & "$] to use "Source" as the sheet in place of "Sheet2".

I have to use LEFT JOIN because I am getting error with only JOIN and later deleted the extra rows from Sheet3 for the records not matching in Sheet1, also you may have to use UNION function since FULL JOIN is not supported in case if you want data from both the table.

Hope this helps for someone...

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=" & ThisWorkbook.Name & ";" & _
         "Extended Properties=Excel 8.0;"
     .Open
 End With

 Dim rs As ADODB.Recordset
 Set rs = New ADODB.Recordset

 rs.Open "SELECT * FROM [Sheet2$] LEFT JOIN [Sheet1$] ON [Sheet2$].[Emp_id] = [Sheet1$].[Emp_id]", cn

 With Worksheets("Sheet3")
     .Cells(2, 1).CopyFromRecordset rs
 End With

 rs.Close
 cn.Close

 End Sub

answered Jun 6, 2016 at 18:00

Chito

3152 gold badges9 silver badges22 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Chito Over a year ago

Instead of Index to get sheet number [SHEET NAME$] also can be used.

Fox · Accepted Answer · 2020-04-02 16:45:48Z

0

cn As New ADODB.Connection 
rec As New ADODB.Recordset
cn.ConnectionString = "Driver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};DBQ=" & _
ActiveWorkbook.Path & Application.PathSeparator & ActiveWorkbook.Name

SQLquery = "SELECT [Sheet2$].[Emp_id],[Sheet2$].[Hobies],[Sheet1$].[Salary] FROM [Sheet2$] INNER JOIN [Sheet1$]  ON [Sheet2$].[Emp_id]=[Sheet1$].[Emp_id]"

rec.Open SQLquery, cn, adOpenKeyset, adLockOptimistic
Sheets("Sheet3").Range("A1").CopyFromRecordset rec

edited Apr 2, 2020 at 16:45

answered Apr 2, 2020 at 16:40

Fox

4174 silver badges3 bronze badges

Collectives™ on Stack Overflow

How to join two excel worksheets based on a column as key using VBA

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related