Results 1 to 4 of 4

Thread: Using ADO to query other Excel Workbooks - Questions
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    Nov 2016
    Posts
    550
    Post Thanks / Like
    Mentioned
    6 Post(s)
    Tagged
    0 Thread(s)

    Default Using ADO to query other Excel Workbooks - Questions

    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/a...ting-database/
    Last edited by JumboCactuar; May 11th, 2019 at 08:42 AM.

  2. #2
    Board Regular
    Join Date
    Nov 2016
    Posts
    550
    Post Thanks / Like
    Mentioned
    6 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Using ADO to query other Excel Workbooks - Questions

    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)

  3. #3
    Board Regular Worf's Avatar
    Join Date
    Oct 2011
    Location
    Rio, Brazil
    Posts
    3,622
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Using ADO to query other Excel Workbooks - Questions

    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
    Excel 2013 / Windows 8.1 (home)
    Excel 2013 / windows 7 (work)


  4. #4
    Board Regular Kyle123's Avatar
    Join Date
    Jan 2012
    Location
    Leeds, UK
    Posts
    2,609
    Post Thanks / Like
    Mentioned
    10 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Using ADO to query other Excel Workbooks - Questions

    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.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •