Uploaded March 2020 | Updated September 2026, 2 weeks ago

---

How to use ADO and SQL with VBA to analyze large Excel worksheet data automatically.
Here's the complete VBA code:
Sub sbADOExample()
Dim sSQLQry As String
Dim Conn As New ADODB.Connection
Dim myrs As New ADODB.Recordset
Dim sconnect As String

sconnect = "Driver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};DBQ=" _
& ActiveWorkbook.Path & Application.PathSeparator & ActiveWorkbook.Name

Conn.Open sconnect

Dim VehicleModel As String, Region As String, CustomerType As String

VehicleModel = InputBox("Enter a vehicle model")

'Table Name = Sheet Name = [Sheet1$]
sSQLQry = "SELECT * From [Sheet1$] WHERE"
If "VehicleModel" NOT EQUAL TO "" Then
sSQLQry = sSQLQry & " [Vehicle Model]='" & VehicleModel & "'"
End If

Region = InputBox("Enter a region name")

If "Region" NOT EQUAL TO "" Then
sSQLQry = sSQLQry & " AND[Region]= '" & Region & "'"
End If

CustomerType = InputBox("Enter a customer type")


If "Customer Type" NOT EQUAL TO "" Then
sSQLQry = sSQLQry & " AND[Customer Type]= '" & CustomerType & "'"
End If

myrs.Open sSQLQry, Conn
'Paste data into sheet2 after clearing any earlier data
Sheet2.Visible = True
Sheet2.Select
Range("dataSet").Select
Range(Selection, Selection.End(xlDown)).ClearContents
Sheet2.Range("A2").CopyFromRecordset myrs

'Close Recordset
myrs.Close

'Close Connection
Conn.Close

End Sub
Note: Instead of 'NOT EQUAL TO' you'll have to use the proper brackets as shown in the video.
Details are also available at exceltrainingvideos.com/using-ado-and-sql-with-vba