封装ADODB数据库查询的Excel VBA类模块

作者:, 发表于

办公自动化

查看该系列所有文章

我贡献的源代码

查看该系列所有文章

2014-03-25 更新:我已经将该类修改成函数形式,并增加新功能,参见更新Excel的数据库查询函数库

关于Excel操纵数据库,我在前面至少写过两篇相关文章:如何利用Excel的数据源功能实现多表合并和SQL查询以及如何动态修改Excel数据源的数据来源和数据源的查询语句。这里再放出一个我平常使用的封装ADODB数据库查询的Excel VBA类模块。

上面这个文件里有类模块Database,以及一个示例。这里简单介绍这个数据库类的使用方法。在使用之前,先需要声明一个类实例

dim db as New Database

然后再如以下方式使用:

db.QueryToCell sql, connection_string, excel_range

其中参数sql是数据库查询语句,excel_range是Excel文件的一个单元格对象(即Range("A1")这样的)。需要重点拿出来说的是connection_string(连接字符串),这个参数告诉函数所需要查询的数据库的信息。比如普通的SQL数据库的connection_string长下面这个样:

"driver={SQL Server};server=ip;uid=username;pwd=password;database=database_name;"

比如如果数据源是Excel文件,那么使用下面这个connection_string:

"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=filename;Extended Properties=Excel 12.0;"

类模块内置了该连接字符串。如果省略connection_string参数,函数将默认使用本Excel文件作为数据源。此时数据表可以是以下形式:

  • 通过名称引用。比如如果定义一个数据区域为Industry,那么select * from industry这样是可行的。这种方法最多支持65535行数据,当数据行数过多时,Excel会提示找不到该数据表。同一张工作表里可以有多个数据表,通过定义不同的名称去引用。
  • 通过工作表名引用。比如一个工作表名为Quotes,那么select * from `Quotes$`这样是可行的。这里工作表名后面的$号表示这是一个工作表。工作表可以包含高达100万行数据。但同一个工作表内只能有一个数据表。
  • 可以通过数据表的地址进行引用。比如select * from `Quotes$A1:B10000`
  • 上面的引号可以用中括号代替。比如select * from [Quotes$A1:B10000]
  • 如果数据表不在目前工作的文件内,需要在上面的数据表名前添加数据文件的路径和文件名,比如select * from `D:\test.xlsx`.`Quotes$`

另外,该类模块还包括下面两个函数,用来获取数据在VB函数内部进行处理。下面这个函数返回二维数组:

res_array = db.QueryToArray(sql, connection_string);

下面这个函数返回单个结果(非数组),如果数据库查询返回多个结果或多个字段,只有最左上方的字段结果会保留,其余字段和结果全被丢弃:

res_single = db.QueryOne(sql, connection_string);

Q.E.D.


上一篇:编辑和发送Lotus邮件的Excel/VBA脚本2012年9月26日
以前发过一个编辑和发送Outlook邮件的Excel/VBA脚本。最近公司不让用Outlook,强制使用IBM Lotus Notes,我又写了一个编辑和发送Lotus邮件的VBA脚本。 这个

下一篇:Excel/VBA的错误处理2013年2月4日
介绍Excel/VBA处理错误的方式、原理,以及编程时的一些注意事项。有大量示例代码。


  • 支持使用微薄、微信和QQ的账户登陆进行评论。由各自网站直接认证,不会泄露你的密码。
  • 登陆后可选择分享评论到所绑定的社交网络,如微薄、人人和QQ空间。
  • 评论提交后无法修改。如需修改,请删除原评论再重新提交。
  • 评论支持LaTeX代码,行内公式请用\(a+b=c\),行间公式请用\[a+b=c\]。公式只支持英文字符。