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

作者: , 共 1513 字
系列:办公自动化

查看该系列所有文章

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

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

[download name="Excel 数据库操作类.xlsm"]

上面这个文件里有类模块 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.

系列: 办公自动化 »
以前发过一个编辑和发送 Outlook 邮件的 Excel/VBA 脚本。最近公司不让用 Outlook ,强制使用 IBM Lotus Notes ,我又写了一个编辑和发送 Lotus 邮件的 VBA 脚本。
键盘流(指尽量使用键盘,少动用鼠标,尤其不要在鼠标和键盘之间来回切换)是我追求的目标。我工作中用的最多的软件是 Excel ,写代码写的最多的是 Excel/VBA。用得多了自然比较关注工作效率,所以我在 Excel 键盘流上略有心得,这里给大家分享一下。网上有很全的快捷键列表,但那不是我想写的,因为太多记不下来不实用。这里只列举一下我在实际工作中的的确确总是在用的,为自己方便,也共享给大伙们。
类似文章:
Matlab 的 database 工具箱只支持 ODBC 数据库连接,在使用之前需要先设置 ODBC 数据源。之前在 Excel 里用的是 ADODB ,使用 ADODB 的好处是无需设置 ODBC 源,似乎效率也要高一些(但我一直没弄懂 ADODB 是什么东西,所以效率这东西我也说不清)。
Excel 多表合并和查询是一个应用很广泛的问题。下面是一个简单的例子,我们需要从两张数据表里,得出每个行业的股票波动率平均值。第一个数据表保存了股票和行业的对应关系,有两列,第一列为股票名,第二列为每只股票对应的行业。第二张表保存了各个股票在各个交易日的收盘价和前收盘价,有四列,第一列是股票名,第二列为交易日,第三列和第四列分别为股票在这个交易日的前收盘价和收盘价。
编程 » Excel, 数据库
在前面的文章里,我已经提到Excel 数据本身可以当做一张 SQL 查询的数据表,并在 Excel 内进行数据库运算操作。数据库查询函数可以用我之前写的Excel 数据库操作函数类。我们可以用以下方式
更新一下之前写的Excel 的数据库类,将其改成函数的形式,调用更简单(省却了生成类实例的步骤)。现在这个代码在工作中用了一年多,已经比较健壮。若有问题,请留言指出或与我联系。
Excel 有一个很有用的功能是直接导入外部数据库或者使用外部数据源建立数据透视表和数据透视图。但比较可惜的是,这个数据源的查询语句是静态的,它无法根据日期自动修改(比如在应用中,我们希望每天获取的外部数据都是当天最新的数据),下面两个函数是修改外部数据源的 VBA 代码,调用它们就可以建立动态的数据源。
编程 » Excel
在编辑 Excel 文件时经常遇到的一个问题是,我这边用得好好地,换台机器就变了个样,或者根本用不了。下面是我在日常工作中总结的一些避免这些情况的小技巧。
编程 » Excel, lotus, VBA
公司使用 Lotus ,我每天都通过 Excel 编写 VBA 代码自动调用 Lotus 发送邮件,发现一个奇怪的现象。一般我们通过下面的 VBA 代码指定邮件收件人:
编程 » Excel, VBA
VBA 的 Date 类型比较奇怪。
如果一个日期(或者时间),如果用字符串,比如"2010-10-04"的形式,各个系统都没有什么区别。在某些时候,将日期用数字表示,将大大增加对日期查找、比较的效率。而如果用数字来表示日期,在不同的系统之间差别就大了。
这个 Excel 模板使用参数法和历史法计算资产组合的VaR,两个函数分别是 ParaVaR 和 HistVaR ,是以前写的VaR Primer的一个实现。具体使用方法可参考模板以及 VBA 的代码注释。
以前发过一个编辑和发送 Outlook 邮件的 Excel/VBA 脚本。最近公司不让用 Outlook ,强制使用 IBM Lotus Notes ,我又写了一个编辑和发送 Lotus 邮件的 VBA 脚本。
投资 » 期权, 期权策略
使用简单的期权,可以组合成各种各样的回报。现实中一些较为复杂的结构式期权通常可以认为是一些普通期权的组合,比如当年造成中信泰富巨亏的澳元外汇结构性期权,基本上可以认为买入一系列的外汇看涨期权,同时卖出一系列的外汇看跌期权。