Excel编程连接数据库.docx

《Excel编程连接数据库.docx》由会员上传分享，免费在线阅读，更多相关内容在工程资料-天天文库。

1、Excel一个重要的数据分析平台，自然就少不了与数据库连接。在Excel上就有直接打开数据源的选项，如下图所示：尽管这种方式用起来很方便，这种连接数据源的方式和其他功能一样，不是很灵活。查到的数据会被放到一个工作表里，并做自动筛选。如果我们有特别的要求，比如希望将数据表的左左上角定位在B10单元格，这种方式就不能满足要求。另外，鉴于SQL的强大，这些“好用”的功能到了高级用户手里就会一无是处。最后，这种方式不能把Excel的数据Insert到数据库中。所以，利用VBA连接并操作数据库就显得很必要了。要相操纵数据库，首先添加对MicrosoftActiveXDataObjectsX.XLibr

2、ary的引用。其中的X.X是版本号。对于本教程使用的功能来讲，版本号是无所谓的——根本用不到那些新功能。这个库是一个把数据库操作对象化的库，使用起来和其他的库很类似。然后就可以写一个Sub来连接数据库了。首先，需要新建一个连接对象。DimconAsNewADODB.Connection注意其中的New关键字用于创建一个新的对象。这也可以写成DimconAsADODB.ConnectionSetcon=NewADODB.Connection然后，就要设置连接字符串了。在这个例子中，我建了一个Access2007的数据库mydb来做实验。这样，连接字符串就是：con.ConnectionStri

3、ng="Provider=Microsoft.ACE.OLEDB.12.0;DataSource=D:MyDocDocumentsmydb.accdb"当然，如果你连接其他的数据库，连接字符串是不一样的，比如连接SQLServer就可能是"Provider=SQLOLEDB;DataSource=[dbsource];DATABASE=[dbname];UID=[user];pwd=[password]"每种数据库都有自己的连接字符串格式，你需要查阅文档才能知道到底该怎么写这个连接字符串。不过连接字符串的一般格式是一致的，即一组被分号;分开的key=value的项目。Provider是

4、指数据库的驱动类型；DataSource是指数据库文件；Database是指数据库名；UID是用户名；pwd是密码；有的时候，需要用Windows集成帐户校验，这时还要加上IntegratedSecurity=sspi。其外还有很多其他的项目，例如通讯方式，永续化方式，字符编码等。这些项目大多可选，如果不写也可以连上数据库，只有在希望对连接方式进行精细调整的情况下才需要写它们。设置好了连接字符串后，就可以用以下代码打开数据库con.Open最常见的数据库使用方式就是查询了。为了存储查询结果，必须首先新建一个记录集对象。DimrsAsNewADODB.RecordSet然后可以定义一条SQL查

5、询语句queryText="select*fromPerson"你可以书写任意复杂的SQL语句，并将其拼成一个字符串。然后，就可以执行查询了。rs.OpenqueryText,con可以看到RecordSet的Open方法在这里接收两个参数，第一个是查询语句字符串，第二个是打开的数据库连接。当执行完这句后，查询结果就已经存储在rs之中了。当然，如果数据量巨大，就会花一些时间。接下来的工作就是将查询结果从rs中读出，并显示在Excel工作表里。这里首先书写列头。Fori=1Tors.Fields.CountActiveSheet.Cells(1,i).Value=rs(i-1).NameNex

6、trs内部会有一个游标（Cursor）。刚查询完后，游标指向查询结果的第一条记录。每条记录有若干列，每一列被称为一个域（Field）。rs(index)就是指当前指向记录的第index个域。上面这段代码遍历每一个域，并将其Name显示出来，放到工作表第一行的对应列中。需要注意，Office的库中的索引一般从1开始计数，而数据库的索引都是从0开始计数的（因为它们都是用C/C++开发的）。所以rs的索引要用i-1。执行效果如下：接下来就是遍历每条记录，将所有的数据显示出来。r=2While(Notrs.EOF)Fori=1Tors.Fields.CountActiveSheet.Cells(r,

7、i).Value=rs(i-1).ValueNextrs.MoveNextr=r+1Wend遍历以rs.EOF=false为条件。当EOF=true时，就说明游标已经到了所有查询结果的末尾。对于每一条记录，可以用rs(index).Value获取它的值。处理完一条记录后，用rs.MoveNext来将游标定位在下一条记录。执行后，可以看到效果：当然，如果你想直接把rs的所有数据显示在单元格里有更简单的做法。比如