SQL+EXCEL数据处理攻略（一）

前段时间自己在公司里做了VBA办公技能培训，其中就涉及到SQL语句在数据查找和汇总上的应用，因此感觉有必要将一些常见的套路和坑加以总结。本帖重点介绍的是在EXCEL VBA中使用SQL语句操作EXCEL工作簿和ACCESS数据库的技巧。

一、连接到数据库

（1）创建ADO对象

在VBA中进行数据库连接，主要是通过ADO对象进行的。ADO（ActiveX Data Objects）是微软公司开发的数据库访问组件，其中最常用的是Connection连接对象和RecordSet记录集对象。Connection对象用于建立数据库连接，RecordSet对象用于存储Connection对象执行SQL查询语句后返回的记录。创建Connection对象方法如下所示：

Dim objCnn As object
Set objCnn = CreateObject("ADODB.Connection")

（2）建立数据库连接

Connection对象通过Open方法建立对数据库的连接，其中最关键的参数是ConnectionString 连接字符串。所有数据连接的参数基本都可通过连接字符串进行设定。
连接不同的数据库时，所需参数不同，连接字符串也不一样。具体的各位可以自行百度，这里只列举本文所需的两种连接字符串：
连接ACCESS数据库：
Provider=Microsoft.ACE.OLEDB.12.0;Jet OLEDB:Database Password=‘密码’;Data Source=文件名
连接EXCEL工作簿：
provider=Microsoft.ACE.OLEDB.12.0;extended properties=‘excel 12.0;’;Data Source=文件名
创建的连接使用完毕后，需要调用Close方法进行关闭。以下代码演示的是如何以数据库方式连接EXCEL工作簿：

Public Sub Connect()
    'Connection对象，Recordset对象，
    Dim objCnn As Object, objRs As Object
    '连接字符串，数据库文件名，记录数据数组
    Dim strCon$, strSource$, arrData
    strCon = "provider=Microsoft.ACE.OLEDB.12.0;extended properties='excel 12.0;';Data Source="
    strSource = ThisWorkbook.Path & "\DATA.XLSX"
    '建立连接
    On Error GoTo e:
    Set objCnn = CreateObject("ADODB.Connection")
    objCnn.Open strCon & strSource
    MsgBox "连接成功！"
    objCnn.Close
    Exit Sub
e:
    Debug.Print err.Number, err.Description
End Sub

（3）执行SQL语句并获取查询结果

使用Open方法建立连接后，就可以使用Execute方法执行SQL语句，该方法返回一个包含了查询结果的RecordSet对象：

 Set objRs = objCnn.Execute("SELECT * FROM [Sheet1$]")

要获取RecordSet对象中的记录数据，常用的办法有两种。第一种是使用RecordSet对象的GetRows方法，该方法会返回一个包含记录的动态数组：

arrData = objRs.GetRows()

第二种是调用Range对象的CopyFromRecordset方法，直接将数据粘贴到单元格：

[A2].CopyFromRecordset objRs

上面获取的都是记录数据，而不包括字段名。如果要获取字段名，可以遍历RecordSet对象的Fields对象集：

For i = 0 To objRs.Fields.Count - 1
	Cells(1, i + 1).Value = objRs.Fields.Item(i).Name
Next i

完整的示例代码如下：

Public Sub Connect()
    'Connection对象，Recordset对象，
    Dim objCnn As Object, objRs As Object
    '连接字符串，数据库文件名，记录数据数组
    Dim strCon$, strSource$, arrData, i%
    strCon = "provider=Microsoft.ACE.OLEDB.12.0;extended properties='excel 12.0;';Data Source="
    strSource = ThisWorkbook.Path & "\DATA.XLSX"
    '建立连接
    On Error GoTo e:
    Set objCnn = CreateObject("ADODB.Connection")
    objCnn.Open strCon & strSource
    '执行SQL
    Set objRs = objCnn.Execute("SELECT * FROM [Sheet1$]")
    '打印字段表头
    For i = 0 To objRs.Fields.Count - 1
        Cells(1, i + 1).Value = objRs.Fields.Item(i).Name
    Next i
    '获取查询结果
    'arrData = objRs.GetRows()
    [A2].CopyFromRecordset objRs
    objRs.Close
    objCnn.Close
    Exit Sub
e:
    Debug.Print err.Number, err.Description
End Sub