利用ADO连接EXCEL，并实现数据的复杂计算

大家好，今日我们继续VBA与数据库的学习，最近的讲解是ADO与EXCEL的连接，从而实现许多方便快捷的操作，这里的EXCEL是作为数据库应用的，在本书数据库的讲解中，连接详细讲解的就只有accdb数据库和excel数据库了，两者各有不同，对于EXCEL而言是非常大众化的数据处理软件，利用的非常广泛，而accdb数据库是一个专门的数据库。两个可以相互的取长补短，比如excel数据库我在讲解的时候，尽可能的不去执行实际打开的操作，只是提取数据的处理。这样可以发挥EXCEL的优点，而避开文件容易改动的缺点。

今日讲解的是如何利用ADO完成相对复杂的数据运算，我们先看下面的EXCEL数据：

这时几种药品在1到6月份的销售情况，记录在15年.xlsx的工作表sheet3中，我们要提出数据，但我们关心的不是每月的销售情况，而关心的是每月销售的增加情况，和每种药品在上半年的销售总额是多少,我们要把每月销售的增减和半年的销售总额汇总出来，而不打开文件，这个程序用ADO连接该怎么写呢？

看下面的代码：

'第36讲，利用ADO,实现EXCEL工作表数据的复杂计算
Sub mynzexcels_5()
    Dim cnADO, rsADO, Z As Object
    Dim strPath, strTable, strSQL As String
    Set cnADO = CreateObject("ADODB.Connection")
    strPath = ThisWorkbook.Path & "\" & "15年.xlsx"
    strTable = "[sheet3$a2:G9]"
    '建立连接,提取数据
    cnADO.Open "provider=Microsoft.ACE.OLEDB.12.0;extended properties='excel 8.0;hdr=no;imex=1';data source=" & strPath
    strSQL = "select F1,F3-F2,F4-F3,F5-F4,F6-F5,F7-F6,F2+F3+F4+F5+F6+F7 from " & strTable
    Set Z = cnADO.Execute(strSQL)
    Range("a:g").ClearContents
    Range("a1:g1") = Array("药品名", "2月-1月", "3月-2月", "4月-3月", "5月-4月", "6月-5月", "半年合计")
    [a2].CopyFromRecordset Z
    cnADO.Close
    Set cnADO = Nothing
End Sub

代码截图；

代码讲解：

1. strTable = "[sheet3$a2:G9]" 这里设定了数据库的取值范围是A2:G9的单元格
2. strSQL = "select F1,F3-F2,F4-F3,F5-F4,F6-F5,F7-F6,F2+F3+F4+F5+F6+F7 from " & strTable 在这句中，就SQL语句我们分别定义了几个列的值分别来自上述的数据范围，这几个列的值是F1: 数据源工作表的第一列；F3-F2: 数据源工作表的第3列减去第2列；F4-F3： 数据源工作表的第4列减去第3列；F5-F4, 数据源工作表的第5列减去第4列；等等，最后的一列是F2+F3+F4+F5+F6+F7，是数据源工作表的6列数值相加。
3. Range("a1:g1") = Array("药品名", "2月-1月", "3月-2月", "4月-3月", "5月-4月", "6月-5月", "半年合计") 这句是把上述数据提取出来后要再安上列的标题。

下面看我们的运行情况：

最后的运行结果：

大家要清楚，为什么要引入ADO来操作数据库和EXCEL呢？操作速度是非常快的。