马红 江苏省广电有线信息网络股份有限公司南京分公司
由于Excel软件具有获取方便,容易操作等特点,使得其在当今社会中几乎成了一种与计算机、万用手册一样的必备工具。Excel除了可以用来制作各种华丽的表格和图标,进行简单的运算之外,还可以当成是“管理个人数据库的工具”,并可以以此为基础进行数据分析,指导实际工作。日常工作中,有很多相对重复的表格或是数据操作类的工作产生了很多工作量,占用了太多的宝贵时间,如果能用VBA来解决这个问题,让原本重复繁杂的工作做到一键搞定,就能大大提高我们的工作效率。
Excel虽然也可以单独当成数据库来使用,不过将其和Oracle数据库专用应用程序结合在一起,可以构建出处理大量数据的大型系统。日常工作中,数据库的操作和执行具有一定的局限性,需要一定的专业技能,如果能跟Excel结合,会非常明显的扩大应用场景,增强工作效率,能使得一些原本非常繁琐而专业的事情变的更加方便可行。
通过VBA连接到Oracle等外部数据库进行数据控制,需要使用到ADO。
ADO是ActiveX Data Object的简称,是一个用来存取数据用的ActiveX控制项的对象,ADO应用非常方便,它在设计上可以用几乎相同的程序代码与各种数据库进行存取。
通过ADO的使用,使得VBA和数据库的交互和通过VBA直接进行数据库记录的读取等操作得以实现。ADO在控制外部数据库的时候,担任了数据库和操作数据应用程序之间的中介角色,如图1所示。
通过OLE DB Provider原件来存取各个数据库,对ADO而言如同英汉词典一样,就算数据库的类型有所改变,又或是新的数据库类型出现,只要准备好OLE DB Provider字典就可以从ADO进行存取,所以说ADO就是一种数据控制的通用接口,通过ADO连接到数据库的框架如图2所示。
图1 ADO作为Excel与外部数据库之间的中介
图2 通过ADO连接到数据库的框架
图3 ADO的简单对象模型
使用ADO将Excel和外部数据库连接,需要使用Connection对象。通过Connection对象的Open方法确立和数据库的连接。形象的说,Connection对象就是一种类似于将Excel和外部数据库连接在一起的线。
在使用Connection对象确定与特定数据库连接之前,我们需要制作连接字符串,连接字符串包括指定数据库地址、数据库类型、数据库用户名称和登录密码等,这些内容需要准确无误。
连接数据库的字符串有基本的格式,基本格式为Provider=<使用的 OLE DB Provider名称>;Data source=<对外部DB的路径>。其中Provider名称根据要连接的不同的数据库类型去确定相应的名称,不容类型的数据库对应不同的OLE DB Provider。表1列举出几种具有代表性数据库引擎的字符串。
如表1 数据库引擎对应的字符串
数据库引擎 字符串
Jet4.0(Access2000以后) Provider=Microsoft.jet.
OLEDB.4.0
SQL serve Provider=SQLOLEDB.1
Oracle Provider=MSDAORA.1
ODBC驱动器 Provider=MSDASQL.1
从表1可以看出,通过VBA去连接Oracle数据库,选取字符串MSDAORA.1。再结合确切的数据库用户名和用户登录密码,依据这些基本信息,根据既定的格式,可以写出连接字符串,如下所示:
表1 数据库引擎对应的字符
"Provider=MSDAORA.1;password=用户登录密码;user id=用户名;data source=数据库名;Persist Security Info=True;"此连接字符串存放于变量定义后,数据库连接指令前。
连接字符串编写好并且确认无误后,应用基本语法“Connection对象.Open ”进行和外部数据库的连接。先声明一个Connection对象为conn,如:Dim conn AS New ADODB.Connection;
定义连接字符串strConn ="Provider=MSDAORA.1;password=用户登录密码;user id=用户名;data source=数据库名;Persist Security Info=True;"
连接数据库的基本格式为:conn.Open strConn,如图4所示。
利用Connection对象和外部数据取得连接后,接下来从获得连接的数据库取得数据,需要使用Recordset对象。Recordset对象是一种存储从数据库中所取得数据记录的容器。
图4 连接数据库的基本格式
使用ADO和SQL语句可以更容易更灵活的获取所需的数据。正常情况下,需要编写专业的SQL脚本在数据库执行,以获取想要的数据。在使用VBA跟外部数据连接的情况下,可以把需要执行的SQL语句放在相应的单元格中,相当于给单元格赋予内容,而内容就是具体的SQL脚本。对于比较复杂的SQL语句,可以合理的分开放在多个单元格中。如果SQL非常简短,也可以直接将SQL语句写在VBA程序中。做好取数据的关键,第一步是要保证调取数据的SQL脚本准确无误,再根据习惯和复杂程度把SQL放在Excel的单元格或是直接写入VBA程序。
在Recordset对象中设置外部数据库特定数据表的引用,可以进行更详细的数据库表控制。首先对特定数据表数据进行处理的时候,可以引用最前面或者最后面乃至任意一处的记录,这里涉及到移动目标记录的方法。表2给出了移动目标记录的方法。当一直重复从一个位置往后移动或者往前移动时,有可能超过最前面或者最后面的记录,然而对于超过第一条或者最后一条记录的记录,当进行取值的时候就会报错。因此需要当前取值不超出最前面和最后面的位置,这可以用BOF或EOF来判断,通过这样的组合去编写循环过程会方便记录的导出。
表2 移动目标记录的方法
表3 判断记录位置的属性
取得外部数据库数据的基本逻辑就是把外部数据库的数据写入相应指定的单元格,此时需要知道数据库结果集的列数和对应的列名,基本语法为:
worksheet.Cells(i,j) = RecordsetObject.Fields(count).
Value
或者是:
按照《建筑节能工程施工质量验收规范》(GB50411-2007)中规定:通风与空调工程安装完成后,在联合试运转及调试结束后应进行系统节能性能的检测,其规定了通风与空调系统节能性能检测主要项目为室内温度、各风口的风量、系统的总风量、空调机组的水流量、空调系统冷热水、冷却水总流量,检测批次为各风口的风量、系统的总风量、空调机组的水流量抽检系统数量的10%,且不得少于1个系统。空调系统冷热水、冷却水总流量为全数检测。
w o r k s h e e t.C e l l s(i,j) = R e c o r d s e t O b j e c t.Fields(fieldName),
然后再配合记录的移动来导出所有数据库结果集。
以上讨论的都是取得结果集的模式,有时在查询数据的类型中,除了在执行后返回结果组的查询外,还有一种在既存数据表中进行数据更改的动态查询,这种不需要取得结果集的查询模式,就必须要使用Command对象。
有时所要做的不仅是对外部数据库的记录原封不动的导出,还需要对外部数据库的记录进行变更,比如添加记录,删除记录,更新某些特定条件的记录等。在进行记录的变更前,需要注意的是要指定Recordset对象的LockType属性,默认状态下设置的是AdLockReadOnly,表示只读,此时无法进行记录的添加变更和删除。将这个属性更改为AdLockPessimistic,以允许打开相应的数据表进行相应的变更操作。
在外部数据库的任意数据表中添加记录时,使用Recordset对象的AddNew方法;删除数据表记录时,使用Delete方法;更改Recordset对象中的记录值时,使用Update方法。
以下对应于操作外部数据库的情景,立足一个简单的实例,给出VBA操作外部数据库基本步骤的演示。
(1)声明并定义Recordset对象;
(2)将需要导入的数据表名称与使用的连接指定为Open方法的参数;
(3)将数据表的数据导入到定义的Recordset对象中;
(4)使用CopyFromRecordset方法将导入的数据写到相应的单元格中,使用此方法时指定要导出数据所在的Recordset对象为参数;
(5)数据导出后,将使用过的Recordset对象和Connection对象进行释放。
列举一个例子来说明。假设Excel表格的sheet1中的单元格A1到A2中存放SQL脚本,定义的Recordset对象rs,定义的Connection对象为conn,则基本语法如下:
有时需要通过VBA控制数据库一次性执行多段相互平行的SQL脚本,为方便起见,会把外部数据库的记录转存为同一个工作簿的多个工作表,然后再进一步把多个工作表另存为多个工作簿。要完成此项工作,首先要明确工作簿的存放目录,然后要明确工作簿的类型,比如是csv类型或者xlsx类型等。把文件保存为csv格式的基本语法如下:ActiveWorkbook.SaveAs Filename:="文件存放目录"& "文件名 " & ".csv", FileFormat:=xlCSV
Excel的VBA与Oracle数据库结合交互进行数据处理的方法的应用日趋广泛,在数据分析中起到至关重要的作用,突破了Oracle数据库对相关使用人员专业性要求高的缺点,极大方便了报表的自动生成。只需把SQL脚本封装到Excel当中,再通过ADO、Connection对象、Recordset对象等专业工具,就可以实现通过VBA操作外部数据库,并且能够对数据库的执行结果记录进行选择,复制以及更改的操作,从而实现了通过Excel的VBA把数据库的执行结果按照预定的方式展现出来。