VBA与数据库——Excel

规则

一说到数据库，一般都会想到那些很专业的数据库，其实Excel本身也可以作为数据库来使用。

虽然我们用Excel的时候可以随意输入数据，但是Excel组织数据也是有它的规则的。

当我们把Excel当作数据库来处理的时候，只要满足数据库处理程序的组织数据的规则，也是可以得到想要的结果的。而如果在输入数据的时候比较随意，操作Excel数据库的程序会按照自己的规则去处理这些数据，可能得到的结果就不是想要的。

这就像平时写VBA代码处理Excel表格，一般都是让VBA程序去适应数据的规则，所以处理数据的VBA程序总要改变，这是因为我们是让VBA程序去适应数据的规则。而数据库的处理程序规则固定后，我们使用数据库是去适应程序的规则。

这样程序不需要总去改变，只要数据保证规律就可以。平时使用VBA去处理Excel数据，也应该尽量让数据保持规范，这样VBA代码就更有通用性了。

换个方式处理Excel数据

直接拿个例子来演示把Excel当作数据库处理：

Sub Test()
    Dim AdoConn As ADODB.Connection
    Dim rst As ADODB.Recordset
    Dim i As Long
    Dim rng As Range
    
    Set rng = Range("A1")
    'Microsoft ActiveX Data Objects 2.8 Library
    Set AdoConn = New ADODB.Connection
    Set rst = New ADODB.Recordset
    
    '打开数据库
    AdoConn.Open "Provider =Microsoft.ACE.OLEDB.12.0;Data Source=" & ThisWorkbook.FullName & ";Extended Properties=""Excel 12.0;HDR=YES"";"

    '执行sql语句，将数据读取到Recordset
    rst.Open "select * from [Sheet1$]", AdoConn
    '输出rst的标题
    For i = 0 To rst.Fields.Count - 1
        rng.Offset(0, i).Value = rst.Fields(i).Name
    Next i
    '从Recordset中读取并输出数据到rng单元格
    rng.Offset(1, 0).CopyFromRecordset rst

    rst.Close
    AdoConn.Close
    Set rst = Nothing
    Set AdoConn = Nothing
End Sub

代码的原理可以先不去理解，只是先感受一下换一个处理Excel的方式。这里我们不是去用Range对象读取单元格的值，直接使用了一个ADODB的东西，调用了它的方法去获取Sheet1的所有单元格中数据到Sheet2中。

ADODB就是一个外部的对象，前期绑定的话先引用Microsoft ActiveX Data Objects 2.8（版本可能不同） Library。

从代码里可以看出，ADODB读取Excel单元格数据的方式其实和使用Open读取文件的方式差不多：

• 打开Open——AdoConn.Open
• 读取Get——rst.Open（CopyFromRecordset rst只是读取到单元格）
• 关闭Close——rst.Close、AdoConn.Close

3个步骤。

从这里就可以看出，把Excel当作数据库处理，调用ADODB去读取数据的时候，其实就是让一个程序去操作Excel文件，这个程序可以解析Excel文件的结构，直接从文件中去读取数据。（演示的是读取当前打开了的Excel，读取未打开的Excel也是一样的）

ADODB应该只是一个操作数据库的接口，真正读取数据的是另外的程序，这个操作数据库的程序一般都是数据库设计的公司开发好的，我们只需要安装对应的程序，然后让ADO拿来用就可以，这种程序都非常的强大，操作速度非常的快，在数据非常大的时候就非常明显。

例子里的数据量非常的少，而且是比较规则的，建议可以试着去多加点数据，并且写一些不规则的数据，比如第一行不写列名、写重复的列名、同一列里又写文本又写数字的试试。这样能够发现数据库的规则对这些不规则的数据造成的一些问题。

这种使用把Excel当作数据库来处理的方式，和原来主要使用Excel对象模型来处理是有很大的不同的，这种时候Excel主要的作用是作为一个操作的界面，这也是使用Excel VBA来处理的一个很方便的地方。

用其他专业的程序语言去操作处理数据库的时候，很多时候还需要写一个窗体，使用一些数据展示的控件。而Excel本身就是一个很好的交互界面，再配合上VBA调用ADODB外部对象来处理数据库，使用起来是非常不错的。

但必须要知道，Excel并不是专业的数据库，很多数据库的功能是没有的，比如创建主键、索引等等，数据量较大的时候，各方面性能都不如专业的数据库。

但是，如果数据很规范的情况下，处理Excel数据的时候，相比我们自己用VBA调用Excel对象模型，结合数组、字典等方式来处理，专业的数据库处理程序会更强大，因为毕竟这些程序都是专业程序员编写的，里面使用了很多好的数据结构等。

所以，不管用不用专业的数据库管理数据，把Excel数据做的很规范，使用VBA调用ADODB的处理方式仍然是高效的。