Created
September 3, 2015 19:09
-
-
Save DBremen/2f136bc48c42baa48ca1 to your computer and use it in GitHub Desktop.
VBA Excel to cross-join multiple sheets without duplicates
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
Sub CrossJoinSheets() | |
Dim cn As ADODB.Connection | |
Dim sql As String | |
Dim outputSheet As Worksheet | |
Dim rs As ADODB.Recordset | |
Set rs = New ADODB.Recordset | |
Set cn = New ADODB.Connection | |
With cn | |
.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;" & _ | |
"Data Source=" & ActiveWorkbook.FullName & ";" & _ | |
"Extended Properties=""Excel 12.0 XML;HDR=Yes""" | |
.Open | |
End With | |
sql = "SELECT DISTINCT * FROM [Sheet1$], [Sheet2$], [Sheet3$]" | |
rs.Open sql, cn | |
Set outputSheet = Sheets.Add | |
outputSheet.Name = "CrossJoined" | |
outputSheet.Range("A1").CopyFromRecordset rs | |
rs.Close | |
cn.Close | |
End Sub | |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment