ExcelVBA在代发高校学生款项中的应用

高校财务部门经常要完成学生奖学金、勤工助学工资、困难补助三项基金款项的发放工作, 此项工作所涉及的学生数量众多、款项总金额较大, 而且每次发放的对象都会有变化。每次发放前, 各院系统计录入需要发放款项的学生信息 (包括学号、姓名和发放金额等) , 由学生处进行汇总, 然后由财务部门根据学号追加银行卡号列然后报银行发放 (表1, 表2) 。

本文介绍了学生三项基金款项发放过程中利用Excel VBA读取SQL Server基础数据库并直接在Excel表格中追加银行卡号的处理方法, 以期抛砖引玉。

1 VBA简介

Visual Basic for Applications (简称VBA) 是新一代标准宏语言, 是基于Visual Basic for Windows发展而来的。它与传统的宏语言不同, 传统的宏语言不具有高级语言的特征, 没有面向对象的程序设计概念和方法。而VBA提供了面向对象的程序设计方法, 提供了相当完整的程序设计语言。VBA易于学习掌握, 可以使用宏记录器记录用户的各种操作并将其转换为VBA程序代码。这样用户可以容易地将日常工作转换为VBA程序代码, 使工作自动化。因此, 对于在工作中需要经常使用Office套装软件的用户, 学用VBA有助于使工作自动化, 提高工作效率。另外, 由于VBA可以直接应用Office套装软件的各项强大功能, 所以, 对于程序设计人员的程序设计和开发更加方便快捷。

2 建立Excel和SQL Server间的通信链接

首先在Excel VBA中加入对“Microsoft ActiveX Data Object” (即ADO) 的引用。ADO主要包括Connection对象、RecordSet对象、Fields对象, 每个对象下边还包括众多的下属子对象、属性及相关的方法。只要通过Connection对象建立与SQL Server的连接即可通过RecordSet对象存取SQL Server中的数据。以下是建立与数据库连接的VBA代码:

3 利用RecordSet对象对关联列进行数据匹配

3.1 获取需要的数据并存储到RecordSet对象中

通过ADO RecordSet Open方法可以将SQLServer数据库中的数据存储到RecordSet对象中, Open方法的语法如下:

recordset.OpenSource, ActiveConnection, CursorType, LockType, Options

其中Source参数可以是S Q L语句, ActiveConnection参数对应一个ADODB.Connection对象, 指定打开Recordset时应该使用的游标类型。

CursorType参数的默认值为A dOpenForwardOnly, 即打开仅向前类型游标。在此处应将游标类型设为adopenkeyset, 否则不支持随后的将要使用的recordset.find方法。

Dim rs1 As New ADODB.Recordset

Dim strSQL As String

’//定义select语句, 选取数据库中的xh (学生学号) , kh (学生银行卡号)

strSQL="select xh, kh from xszd"

'//将银行卡号和学号读入记录集, 注意CursorType参数的设置

r s 1.O p e n s t r S Q L, c n d b a s e, adOpenKeyset, adLockOptimistic

3.2 获取RecordSet中的银行卡号并在Excel中追加银行卡号列

For i=StartLine To EndLine

rs1.MoveFirst

rs1.Find"xh='"&myxh&"'"

If Not rs1.EOF Then

Range (BankCard&CStr (i) ) .Value=Trim (rs1.Fields ("kh") )

Else

Range (BankCard&CStr (i) ) .Value="无此学号或卡号"

End If

Next

rs1.Close

Set rs1=Nothing

cndbase.Close

Set cndbase=Nothing

4 实际应用中的技巧

4.1 利用个人宏工作簿使程序随E x c e l启动

个人宏工作簿, 是为宏而设计的一种特殊的具有自动隐藏特性的工作簿。第一次将宏创建到个人宏工作簿时, 会创建名为“PERSONAL.XLS”的新文件。如果该文件存在, 则每当EXCEL启动时会自动将此文件打开并隐藏在活动工作簿后面 (在“窗口”菜单中选择“取消隐藏”后, 可以很方便地发现它的存在) 。通过个人宏工作簿, 宏在多个工作簿都能使用。个人宏工作簿保存在“XLSTART”文件夹中, 具体路径为:C:WINDOWSProfilesApplication DataMicrosoftExcelXLSTART。可以以单词“XLSTART”查询。如果存在个人宏工作簿, 则每当EXCEL启动时会自动将此文件打开并隐藏。因为它存放在XLSTART文件夹内。

摘要：基于Excel VBA和SQL Server的数据交换能力, 充分利用Excel的灵活的表格处理功能, 方便的进行相关信息列的追加, 并且最终将Excel文件导出为款项银行代发需要的文本格式。文中对基于Excel VBA和SQL Server的数据处理的实现方法及步骤作了相应的论述。

关键词：Excel,VBA,数据库,数据处理