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:
https://amzn.to/3xnqT6R
Get a Microsoft Office 365 Personal Subscription with this Amazon link:
https://amzn.to/3aV9GtL
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
