How to use SQL in excel VBA macros?

Using SQL queries is better way to handle large amount of data in database applications. also, it generates result within a seconds. As, we aware the SQL queries only can be used in Microsoft Access and Microsoft SQL server applications.

In fact, SQL queries can be used in Excel VBA projects to handle large amount of data. Which will enables you to optimize your code for time complexity and output will be generated in fraction of seconds. Which is the core requirement of any VBA automation project.


Moreover, it reduces coding time to write large amount of code for data calculation. It helps you to perform data transformation within the time span which is actually allocated for project development,

Now, We will see how can we use SQL queries in our VBA automation projects. Please refer below code snippets which demonstrate SQL implementation in Excel macro.

Option Explicit

Public cnn As New ADODB.Connection
Public rs As New ADODB.Recordset
Public strSQL As String
Public ErrStatus As Integer

Public Sub OpenDB(ByVal fName As String)
On Error GoTo Handler
ErrStatus = 0
        If cnn.State = adStateOpen Then cnn.Close
        cnn.ConnectionString = "Driver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};DBQ=" & fName
        cnn.Open
        
Handler:
         Exit Sub
End Sub

Public Sub closeRS()
    If rs.State = adStateOpen Then rs.Close
    rs.CursorLocation = adUseClient
End Sub


Sub Fetch_Data_Using_ADODB_Connection()
' Author: Dreams24
' Written for VBA Tricks and tips blog
' https://vbatricksntips.com

Dim strSQL As String

' closes recordset if it is open
    
    closeRS

' Assign path of workbook from which data to be fetched

    OpenDB (ThisWorkbook.Path & "\" & ThisWorkbook.Name)

' Write your query to get your specific column data in output
' Where Raw_Data is your sheet name from which data is fetched.
' Don's forget to append $ symbol with sheet name
' refer below query example

        strSQL = "Select [Column1], [Column2], [Column3] FROM [Raw_Data$]"

        rs.Open strSQL, cnn, adOpenDynamic, adLockPessimistic
        
' Activate your desired sheet to get the output from above query

        ThisWorkbook.Sheets("Output").Activate
        
' Select cell in which data to be fetched
        
        Sheets("Output").Range("A1").CopyFromRecordset rs

        rs.Close
        cnn.Close

        Set rs = Nothing
        Set cnn = Nothing

End Sub