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
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