Fast way to query listobject vba

Question

I use excel 2016 and need to get a value from list object. There are several columns. For example three, and I need to get the value in column1 where column2=smth, column3=smth. How can I do this fast?

I'm not 100% sure I understand the question. Do you mean like this? ActiveWorkbook.Worksheets("Sheet1").ListObjects.Item(1).Value Where 1 is the object. msdn.microsoft.com/en-us/vba/excel-vba/articles/… — HackSlash
@HackSlash No. I don't want to find a ListObject. I want to do something like sql select where query with multiple conditions inside the values of a particular ListObject — Роман Коптев
How fast is fast enough? Did you try something and that was too slow? — Tim Williams
@Tim Williams I have tried using power query, match/index and pivot tables. I have a rather big table to look up, and (in my version) those methods worked visible slowly, or there were needed to do many manipulations on worksheet before I had values in vba variables — Роман Коптев
Match should be pretty fast - alternatives might depend on the size of the table and how many lookups you need to do. — Tim Williams

HackSlash HackSlash · Accepted Answer · 2018-06-29T16:37:26

If you want SQL syntax and speed in VBA then you need to use the ADO DB connector. In my experience it was 4x faster than using native Excel functions to find the data.

It's a lengthy topic that requires you read multiple articles. Much more information than should fit in to one SO post.

Here is an example article: https://technet.microsoft.com/en-us/library/ee692882.aspx

Here is the code from that example:

    Const adOpenStatic = 3
    Const adLockOptimistic = 3
    Const adCmdText = &H1
    
    Dim objConnection As Object
    Set objConnection = CreateObject("ADODB.Connection")
    objConnection.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
        "Data Source=C:\Scripts\Test.xls;" & _
        "Extended Properties=""Excel 8.0;HDR=Yes;"";"
    
    With CreateObject("ADODB.Recordset")
        .Open "Select * FROM [Sheet1$] Where Number = 2", _
        objConnection, adOpenStatic, adLockOptimistic, adCmdText
    
        Do Until .EOF
            Debug.Print .Fields.Item("Name"), .Fields.Item("Number")
                
            .MoveNext
        Loop
    
        .Close
    End With
    
    objConnection.Close

Fast way to query listobject vba

1 Answers