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

作者:
系列:办公自动化

查看该系列所有文章

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 键盘流上略有心得,这里给大家分享一下。网上有很全的快捷键列表,但那不是我想写的,因为太多记不下来不实用。这里只列举一下我在实际工作中的的确确总是在用的,为自己方便,也共享给大伙们。
以前发过一个 编辑和发送 Outlook 邮件的 Excel/VBA 脚本 。最近公司不让用 Outlook ,强制使用 IBM Lotus Notes ,我又写了一个编辑和发送 Lotus 邮件的 VBA 脚本。
投资 » 期权, 期权策略
使用简单的期权,可以组合成各种各样的回报。现实中一些较为复杂的结构式期权通常可以认为是一些普通期权的组合,比如当年造成中信泰富巨亏的澳元外汇结构性期权,基本上可以认为买入一系列的外汇看涨期权,同时卖出一系列的外汇看跌期权。