Do you want to use SQL in Excel without any outside installation for Windows? Well, check out this video where I show you how you can create your very on SQL user defined function and use it in any workbook!
Website for the Connections String: www.connectionstrings.com/
Code:
'This UDF "sql" function will be used to extract data from tables through SQL queries
Function sql(workbook_path As String, sql_statement As String) As Variant
Dim active_connection As Object
Dim record_set As Object
Dim data_array() As Variant
Dim header_array() As Variant
Dim result_array() As Variant
Dim i As Long
Dim j As Long
Dim num_rows As Long
Dim num_cols As Long
On Error GoTo error_handler
'Create the Connection and Recordset objects
Set active_connection = CreateObject("ADODB.Connection")
Set record_set = CreateObject("ADODB.Recordset")
'Set the connection string
active_connection.ConnectionString = _
"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & workbook_path & ";" & _
"Extended Properties=""Excel 12.0;HDR=YES;"";"
'Open the connection
active_connection.Open
'Open the recordset
record_set.Open sql_statement, active_connection, 3, 3 'adOpenStatic, adLockOptimistic
'Check if the recordset is not empty
If Not record_set.EOF Then
'Get the header names into an array
num_cols = record_set.Fields.Count
ReDim header_array(1 To num_cols)
For i = 1 To num_cols
header_array(i) = record_set.Fields(i - 1).Name
Next i
'Get the recordset data into an array
data_array = record_set.GetRows()
'Determine the size of the result array (including headers)
num_rows = UBound(data_array, 2) + 1
ReDim result_array(1 To num_rows + 1, 1 To num_cols)
'Add headers to the result array
For j = 1 To num_cols
result_array(1, j) = header_array(j)
Next j
'Add data to the result array
For i = 1 To num_rows
For j = 1 To num_cols
'Handle potential blank cells
If IsNull(data_array(j - 1, i - 1)) Or IsEmpty(data_array(j - 1, i - 1)) Then
result_array(i + 1, j) = ""
Else
result_array(i + 1, j) = data_array(j - 1, i - 1)
End If
Next j
Next i
'Return the result array
sql = result_array
Else
'Return a single cell array with "No records found" message
sql = Array("No records found.")
End If
'Cleanup
record_set.Close
active_connection.Close
Set record_set = Nothing
Set active_connection = Nothing
Exit Function
error_handler:
'Handle errors
sql = Array("Error: " & Err.Description)
If Not record_set Is Nothing Then
If record_set.State = 1 Then record_set.Close
End If
If Not active_connection Is Nothing Then
If active_connection.State = 1 Then active_connection.Close
End If
Set record_set = Nothing
Set active_connection = Nothing
End Function
Chapters:
0:00 Intro
0:33 Code Showcase
3:48 Example 1
9:46 Example 2
13:13 Example 3
14:31 Example 4
16:33 Get Data From Closed Files
17:39 Conclusion
Follow me on Instagram: / meraz_mamun
Follow me on LinkedIn: / merazmamun