Excel VBA Create SQL JOIN Query on Workbook

This video covers how to create an SQL Join Query on multiple tables within an Excel Workbook. In this example we have 2 tables on 2 sheets. The first table contains a dataset of loan records. On of the fields in this first table is called "Location" and just has a Branch Number. The second table contains the City/State Locations of each Branch Number. The Objective is to create code that will allow end users to Join these 2 tables together and Query the dataset in a single results output.

Learn More about Excel VBA & Macros with this Book on Amazon:

Get a Microsoft Office 365 Personal Subscription with this Amazon link:

DISCLAIMER: As an Amazon Affiliate I get a commission for every purchase via these links.

#ExcelVBASQLJoinQuery
#ExcelVBACreateJoinQuery

The Code used in this video is below:

Sub NewQuery()

Dim conn As New ADODB.connection
conn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & ThisWorkbook.FullName & _
";Extended Properties=""Excel 12.0;HDR=YES;"";"

Dim rSht As Worksheet
Set rSht = ThisWorkbook.Worksheets("Results2")
Dim Query As String
Query = rSht.Range("H5").Value

Dim rs As New ADODB.Recordset
rs.Open Query, conn

With rSht
.Range("A:G").Clear
For i = 0 To rs.Fields.Count - 1
.Cells(1, i + 1) = rs.Fields(i).Name
Next i
.Range("A2").CopyFromRecordset rs
End With

conn.Close

End Sub