Using ADO and SQL with VBA

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” <> “” 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” <> “” 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
Range(Selection, Selection.End(xlDown)).ClearContents
Sheet2.Range(“A2”).CopyFromRecordset myrs

‘Close Recordset

‘Close Connection

End Sub

Published by

Dinesh Kumar Takyar

Welcome to! My aim is to help you learn MS Excel including VBA. I always felt that a good way to learn something was to find solutions to problems in that domain. That is why I share these Excel videos with you. Mostly these are questions asked during my corporate training sessions. I've been training individuals and companies in computers since 1991. I did my B. Sc. (Vordiplom), M. Sc. (Diplom) and Ph.D. (Dr. rer. nat.) from Hamburg, Germany. The best thing about solving some of my visitor's questions is that I also gain new insights. For more Excel VBA solutions you may like to visit my YouTube channel: For a structured Excel VBA training course online you can visit:

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.