How to Use a SQL Function in ANY Excel Workbook! (No Outside Installation Required)

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: https://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