查询ListObject VBA的快速方法

本文介绍了查询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.

这是示例文章: technet.microsoft/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