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

[@mikeshellito2282]

I like your function. Is it possible for you to modify it so that it works on Excel Tables (ListObjects)?

[@merazmamun-analytics]

I am glad to hear that you like my function and that is a good question about ListObjects.
It is certainly possible and I have seen it mentioned in other website. The trickiest part is finding a way to either update the UDF to take into account ListObjects or being able to reference the table / ListObject directly into the 2nd argument of the sql function.
When I tried referencing the table name directly, I got an error message from the function so there is probably a different syntax involved.
I have been spending a couple of hours trying to figure out how I can update the sql function to work with both ranges and listobjects without making the function be clunky and actually be usable for both situations.
If you want to try this yourself, try using the links below to see how you can update the function to include listobjects:
Link 1: stackoverflow.com/questions/69504405/excel-listobject-format-as-table-in-sql
Link 2:
stackoverflow.com/questions/47029764/use-table-name-in-sql-query-in-vba-excel
In the meantime, I am still figuring out how to include listobjects without making the function look or go bad.
Hope that helps!

[@mikeshellito2282]

I have been playing with this for some time now. The trick appears to be getting the tables address and converting it into a range name, which can be used directly in a SQL statement. I have gotten close to being able to do that, but in a clunky manner.

[@s.triyambakpatro7341]

Excellent. Cant we use this as an Add-in?

[@merazmamun-analytics]

@s.triyambakpatro7341 thank you!
Yes, you can use this use defined function on an add-in file. I created the Excel Add-In file beforehand. You can save an excel file as XLAM (If you have not done so already) and just paste the code (from my description) directly into the VB Editor.
If you enable the add-in file, you should be able to use the user defined function in any workbook.
Please note that this should work for windows, but not sure about MAC as you would need still need to be able to reference the active x data object library in VBA.
If you like this video, then please show this to others who might be interested and let me know if there are any other videos you want to see!
Hope that helps!