查询ListObject VBA的快速方法

问题描述：

我使用excel 2016，需要从列表对象获取值.有几列.例如三，我需要在column1 = column3 = smth的column1中获取值.我该如何快速做到这一点?

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?

答

如果要在VBA中使用SQL语法和速度，则需要使用ADO DB连接器.以我的经验，它比使用本机Excel函数查找数据的速度快4倍.

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.

这是一个冗长的主题，需要您阅读多篇文章.大量信息超出了适合一个SO职位的范围.

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

这是示例文章: https://technet.microsoft.com/en-us/library/ee692882.aspx

这是该示例中的代码:

Const adOpenStatic = 3
Const adLockOptimistic = 3
Const adCmdText = &H0001

Set objConnection = CreateObject("ADODB.Connection")
Set objRecordSet = CreateObject("ADODB.Recordset")

objConnection.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
    "Data Source=C:\Scripts\Test.xls;" & _
        "Extended Properties=""Excel 8.0;HDR=Yes;"";" 

objRecordset.Open "Select * FROM [Sheet1$] Where Number = 2", _
    objConnection, adOpenStatic, adLockOptimistic, adCmdText

Do Until objRecordset.EOF
    Wscript.Echo objRecordset.Fields.Item("Name"), _
        objRecordset.Fields.Item("Number")
    objRecordset.MoveNext
Loop

objRecordset.Close
objConnection.Close

查询ListObject VBA的快速方法

相关推荐