Using ADO to query other Excel Workbooks - Questions

[JumboCactuar]

Hi,
i found this great code which allows you to query the current workbook, and also closed workbooks. Allows you to SQL query which i am finding a better way of "advanced filtering" data in the same workbook. Are there any limitations to this?

Also if using ADO on an external workbook (closed or open) , does it open it read only? Or would it show as in use if another user opened the workbook whilst you run this code

'Add reference for Microsoft Activex Data Objects Library

Sub sbADO()
Dim sSQLQry As String
Dim ReturnArray
Dim Conn As New ADODB.Connection
Dim mrs As New ADODB.Recordset
Dim DBPath As String, sconnect As String

DBPath = ThisWorkbook.FullName

'You can provide the full path of your external file as shown below
'DBPath ="C:\InputData.xlsx"

'Using MSDASQL Provider
'sconnect = "Provider=MSDASQL.1;DSN=Excel Files;DBQ=" & DBPath & ";HDR=Yes';"

'Using Microsoft.Jet.OLEDB Provider - If you get an issue with Jet OLEDN Provider try MSDASQL Provider (above statement)
sconnect = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & DBPath _
    & ";Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1"";"
    
Conn.Open sconnect
    sSQLSting = "SELECT * From [DataSheet$] WHERE Sales >3000"  ' Your SQL Statement (Table Name= Sheet Name=[DataSheet$])
    
    mrs.Open sSQLSting, Conn
        '=>Load the Data into an array
        'ReturnArray = mrs.GetRows
                ''OR''
        '=>Paste the data into a sheet
        ActiveSheet.Range("A2").CopyFromRecordset mrs
    'Close Recordset
    mrs.Close

'Close Connection
Conn.Close

End Sub

Source is here if anyone wants to try out the example files provided there: https://analysistabs.com/excel-vba/ado-sql-macros-connecting-database/

 

[JumboCactuar]

Also if using the code in the same workbook multiple times, it would be a lot of code.
Instead, would it be possible to create a UDF where I can simply pass the source / variables and query?

(Sorry couldn't edit)

 

[Worf]

The UDF problem is that you do not know how many cells to select when entering the array formula.
Maybe a subroutine with parameters is a better idea.

Public Function Jumbo(sn$, fld$, t$) As Variant
Dim Conn As New ADODB.Connection, mrs As New ADODB.Recordset, DBP$, ra
DBP = ThisWorkbook.FullName
Conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & DBP & _
";Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1"";"
mrs.Open "SELECT * From [" & sn & "$] WHERE " & fld & " >" & t, Conn
ra = mrs.GetRows
mrs.Close
Conn.Close
Jumbo = Transp(ra)
End Function


Function Transp(v) As Variant
Dim X&, Y&, Xupper&, Yupper&, ta()
Xupper = UBound(v, 2)
Yupper = UBound(v, 1)
ReDim ta(Xupper, Yupper)
For X = 0 To Xupper
    For Y = 0 To Yupper
        ta(X, Y) = v(Y, X)
    Next
Next
Transp = ta
End Function

 

[Kyle123]

The biggest downside to what you are doing is the memory leak issue. The post you link to doesn’t mention it, but there is an old well known bug that leads to a memory leak when querying the open workbook, this doesn’t apply to other workbooks. So a possible workaround would be to copy your data to another workbook and query that.