Using ADO to query other Excel Workbooks - Questions

JumboCactuar

Well-known Member
Joined
Nov 16, 2016
Messages
677
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

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/
 
Last edited:

Some videos you may like

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".

JumboCactuar

Well-known Member
Joined
Nov 16, 2016
Messages
677
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

Well-known Member
Joined
Oct 30, 2011
Messages
3,941
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.



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

Well-known Member
Joined
Jan 24, 2012
Messages
2,702
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,096,146
Messages
5,448,601
Members
405,522
Latest member
NomanAziz

This Week's Hot Topics

Top