Excel实战技巧67：在组合框中添加不重复值（使用ADO技巧）

很多情况下，我们需要使用工作表中的数据来填充组合框，但往往这些数据中含有许多重复值。如何去除重复值并得到唯一值，这是一个永恒的话题，大家也会用到各式各样的方法得到结果。本文讲解一种技巧，使用Recordset（记录集）来获取唯一值并将其填充到组合框中。

示例数据如下图1所示。在工作表中有一个组合框，需要包含列A中的省份列表，但是列A中有很多重复的省份数据。

图1

这里可以使用简单的记录集快速提取不同的省份名并将其装载到组合框。单击功能区“开发工具”选项卡中“插入”按钮下ActiveX控件中的“组合框”，在工作表中插入一个组合框，可以看到Excel将其自动命名为“ComboBox1”，如下图2所示。

图2

按Alt+F11组合键，打开VBE，插入标准模块并输入代码如下：

Sub FillCombox()
    Dim Myconnection AsConnection
    Dim Myrecordset As Recordset
    Dim MyWorkbook As String
   
    Set Myconnection = NewConnection
    Set Myrecordset = NewRecordset
   
    '识别引用的工作簿
    MyWorkbook =Application.ThisWorkbook.FullName
   
    '打开对工作簿的连接
    Myconnection.Open"Provider=Microsoft.Jet.OLEDB.4.0;" & _
    "Data Source="& MyWorkbook & ";" & _
    "ExtendedProperties=Excel 8.0;" & _
    "Persist SecurityInfo=False"
   
    '将所选区域装载到记录集中
    Myrecordset.Open"Select Distinct [省份] from [Sheet1$A1:D50]", Myconnection,adOpenStatic
   
    WithActiveSheet.ComboBox1
        .Clear
        Do
            .AddItemMyrecordset![省份]
           Myrecordset.MoveNext
        Loop UntilMyrecordset.EOF
    End With
End Sub

这段代码运行速度相当快！可以在任何事件或过程中调用它们，例如工作簿打开事件、查询刷新事件或者按下按钮后。

运行或调用过程后，在工作表中单击组合框右侧下拉按钮，结果如下图3所示。

图3

说明

1.示例中使用的是ActiveX组合框控件，如下图2所示。

2.需要在VBE中设置对Microsoft ActiveX Data Objects Library的引用，如下图4所示。

图4

3.可以使用如下所示的命名区域代替硬编码单元格区域：

Myrecordset.Open “Select Distinct [省份] from [命名区域]”

4.可以编写VBA代码遍历数组来获取唯一值。然而，上面的方法更容易，并且使用记录集允许从装载的记录集中快速调整查询来捕获另一个字段或者创建另一个组合框。

ADO记录集基础知识概要

学习ADO基础知识时，可以将ADO视为帮助完成两类任务的工具：连接到数据源和指定要处理的数据集。这可以使用调用一个连接字符串完成。对于ADO初学者来说，先集中了解最常用的参数：Provider、Data Source、Extended Properties。

1.Provider：告诉VBA正在使用哪种类型的数据源。当使用Excel或Access 2007作为数据源时，Provider语法：Provider=Microsoft.ACE.OLEDB.12.0。如果数据处理需要运行在没有Office 2007的计算机上，需要使用早期版本的Access和Excel提供者版本：Provider=Microsoft.Jet.OLEDB.4.o。

2.Data Source：告诉VBA在哪里找到包含所需数据的数据库或工作簿。使用Data Source参数，要传递完整的数据库或工作簿路径。例如：Data Source=C:\MyDirectory\MyWorkbook.xlsx。

3.Extended Properties：当连接到Excel工作簿时使用。告诉VBA数据源来自数据库。当处理Excel 2007工作簿时，参数为：Extended Properties=Excel12.0。如果数据处理需要运行在没有Office 2007的计算机上，将需要使用Excel早期版本的Extended Properties：Extended Properties=Excel8.0。

当在Excel中操作时，可以使用两类连接字符串之一。使用第一类连接字符串（即使用Microsoft.Jet.OLEDB），有助于避免向后兼容问题，而且比Microsoft.ACE快3倍。

连接到Excel2003或Excel 2007（及以上）工作簿（推荐）

"Provider=Microsoft.Jet.OLEDB.4.0;" & _

"Data Source=C:\MyExcelWorkbook.xls;" & _

"Extended Properties=Excel 8.0"

连接到Excel2007（及以上）工作簿

"Provider=Microsoft.ACE.OLEDB.12.0;" & _

"Data Source=C:\MyExcelWorkbook.xlsx;" & _

"Extended Properties=Excel 12.0"