证券公司法人清算系统解决方案

摘 要：本文以证券公司法人清算业务为背景，把多个Office应用程序的特性集成到一个解决方案中，从而提高和促进了用户的生产效率。

关键词：Access、Excel、Outlook、Automation、VBA、DAO

1、引言

每一个Microsoft Office应用程序都包含了一组旨在帮助开发人员完成一组相关任务的强大工具。例如：Microsoft Access提供了强大的数据管理以及查询能力;Microsoft Excel提供了数学分析以及报告工具;Microsoft Outlook提供了发送和接受电子函件、计划、合同以及任务管理；而Microsoft Word支持用户创建和维护文档，跟踪不同用户间的文档版本、创建窗体和模板等。以上应用使单个Office应用程序具备可编程能力以及使得创建集成Office解决方案成为可能的关键技术是称之为Automation的组件对象模型(COM)技术。Automation允许开发人员使用Visual Basic for Application(VBA)代码创建和控制由支持相应编程接口的应用程序、动态链接库、ActiveX控件提供的软件对象。VBA和Automation支持编写独立的Office应用程序。例如：可以在Access中创建一个隐藏的Excel实例，用它对Access数据完成数学运算和分析操作。

2、法人清算系统概述

上海证券中央登记结算公司在中央交收体制下实行会员法人结算制度。上海证券交易所的会员单位均以法人名义在登记公司开立结算帐户，统一办理其所有下属机构在上海证券交易所进行的证券交易的清算交收业务。

证券公司（法人）财务部每天上午通过沪、深法人专用平台拨号上网接收法人清算数据文件。清算文件包含有该公司下属交易席位各项交易费用明细。需要分类汇总：先统计出各席位各项费用，再统计出各营业部费用，最后汇总出公司各项费用。对账、打印、传真给下属营业部，供其对账。根据统计费用，划拨金额，以保障公司备付金。

现在证券公司通过兼并重组，规模日趋庞大，下属营业部少则十几家，多则几十，甚至上百家，所以上述流程由人工处理完成已越来越无法胜任。

综合运用Office应用程序，上述工作得以快捷、准确完成，使以前几个人花费近一上午的工作在十几分钟就可以完成。

法人清算系统业务流程见附图。

3、法人清算系统的设计结构

系统主要由三部分组成：准备数据、处理数据、发送数据。

3.1 准备数据

沪深清算文件为压缩文件，接收完毕后由系统自动完成更名、解压，生成DBF文件，同时完成数据备份工作。

3.2 处理数据

系统主要部分，选用Access为主程序，功能包括：

● 数据导入，生成当日所需明细数据表 ● 当日划拨 ● 各类明细、汇总报表 ● 生成下属营业部所需数据文件，为Excel文件 ● 生成邮件，准备发往各营业部

对于开发运行小型商务系统的应用程序来说，Access是一个较好的平台，它的向导有助于开发人员轻松地快速构造出应用程序的框架；由于它支持代码模块，因此，开发人员很容易建立可重用函数的代码库；又由于它支持在窗体和报表之后添加代码，因此它具有强大的窗体和报表自定义功能。 之所以选用Excel文件为下发文件格式，因为下发数据包括深、沪明细数据和各类汇总数据，可作为工作簿中各工作表，格式清晰，且各营业部可再次利用Excel工具处理，得到想要各类数据。

3.3 发送数据

启动Outlook，发送邮件。

4、法人清算系统的关键技术

4.1 用代码导入外部数据

Access的DoCmd对象提供了三种导入外部数据的方法，它们是： TransferDatabase、TransferText和TransferSpreadsheet 下面的示例使用DoCmd对象的TransferDatabase方法从数据库导入数据： Sub ImportDatabase() DoCmd.TransferDatabase _ TransferType:=acImport，_ '执行转换的类型 DatabaseType:="dBase III"，_ '导入数据库的类型 DatabaseName:=APPPATH，_'数据库的名称 ObjectType:=actable，_'导入对象的类型 Source:="Customer"，_'导入源对象的名称 Destination:="tblCustomer"，_'导入目标对象的名称 StructureOnly:=False '只导入表的结构，还是结构、数据都导入 End sub

4.2 数据访问技术

本系统选用DAO(Data Access Objects)技术。

DAO代码可以创建、修改和删除Jet引擎对象，使用它可以完成以下工作：

● 分析已存在的数据库结构

● 增加或修改表和查询

● 创建新数据库

● 通过修改查询所基于的SQL来改变查询定义

● 遍历记录集

● 修改表数据

4.3 展示数据

在Office解决方案中有许多不同的展示数据的方法，如何建构报表依赖于解决方案是如何设计的以及数据存储在什么地方。本系统解决方案以Access为基础，用户可以通过Access窗体和数据访问页面操作数据；同时若需要在Excel中观察和操作数据，以便以最大的灵活性来创建定制报表，可以把数据从Access中导入到Excel。

在Access运行的代码里使用Automation创建Excel的查询表QueryTable或PivotTable报表。使用Automation可以使用户对从数据库到Excel转换数据的过程进行更多的控制，还可以使用Automation来格式化报表，过滤数据，创建统计图等。用户能够在Access中简单地单击一个按钮，就得到Excel中已格式化的报表。

下面的示例创建一个新的数据库查询并把数据返回到工作表，建立动态报表。

要在VBA中创建一个新的查询表，使用QueryTables集合的Add方法。QueryTables集合从属于Worksheet对象，并且包含了该工作表的所有QueryTable对象。创建了一个新的查询表后，必须使用QueryTable对象的Refresh方法在查询表中显示数据。 Dim dbReset As Database Dim rstReset As Recordset Dim wksNew As Excel.Worksheet Dim qtbData As Excel.QueryTable ...... Set rstReset = dbReset.OpenRecordset("营业部") Set qtbData = _ wksNew.QueryTables.Add(rstReset， wksNew.Range("A4"))

Set qtbData = wksNew.QueryTables.Add( _ Connection:=rstReset， _ Destination:=wksNew.Range("A4")) With qtbData .FieldNames = False .RowNumbers = False .FillAdjacentFormulas = False .PreserveFormatting = True .RefreshOnFileOpen = False .BackgroundQuery = False .RefreshStyle = xlInsertDeleteCells .SavePassword = True .SaveData = True .AdjustColumnWidth = False .RefreshPeriod = 0 .PreserveColumnInfo = True .Refresh BackgroundQuery:=False End With qtbData.Refresh

4.4 使用Microsoft Outlook对象

可以以三种方式使用Outlook的对象模型：

● 编写在本地工程文件或与Outlook本地安装相关联的COM加载项中运行的VBA代码。

● 使用Outlook窗体中自带的脚本环境，该窗体用于显示诸如消息和约会这样的项目。

● 通过Automation在其它Office应用程序或支持VBA的其它应用程序中使用Outlook。

本系统使用第三种方式。在使用VBA访问Outlook对象、方法、属性之前，必须首先单击Visual Basic编辑器的Tools菜单项下的Reference来设置对Microsoft Outlook对象库的引用。

下面的示例InitializeOutlook过程创建一个新的、隐藏的Outlook实例；CreateMail过程创建一个邮件消息，设置收件人、附件、主题和消息内容，然后发送邮件。

Public golapp As Outlook.Application Public gnspNamespace As Outlook.Application

Function InitializeOutlook() As Boolean ' This function is used to initialize the global Application On Error GoTo Init_Err Set golapp = New Outlook.Application InitializeOutlook = True Init_End: Exit Function Init_Err: InitializeOutlook = False Resume Init_End End Function

Function CreateMail(astrRecip As Variant， _ strSubject As String， _ strMessage As String， _ Optional astrAttachments As Variant) As Boolean Dim objNewMail As Outlook.MailItem Dim blnResolveSuccess As Boolean

On Error GoTo CreateMail_Err

If golapp Is Nothing Then If InitializeOutlook = False Then MsgBox "Unable to initialize Outlook Application " _ & "or NameSpace object variables!" Exit Function End If End If

Set golapp = New Outlook.Application Set objNewMail = golapp.CreateItem(olMailItem)

With objNewMail .Recipients.Add astrRecip blnResolveSuccess = .Recipients.ResolveAll .Attachments.Add astrAttachments .Subject = strSubject .Body = strMessage If blnResolveSuccess Then .Send Else MsgBox "Unable to resolve all recipients. Please check " _ & "the names." .Display End If End With CreateMail = True

CreateMail_End: Exit Function CreateMail_Err: CreateMail = False Resume CreateMail_End End Function

4.5 划分数据库对象

把包含表的数据库称为表数据库，而包含其它对象的数据库称为应用程序数据库。通过从应用程序数据库链接到表数据库，就把这两个数据库连接起来了。采用这种策略的理由是：

● 可维护性

● 性能

● 可扩展性

如果已经设计了应用程序，并且在同样的数据库中包括了所有的表和其他数据库对象，可以使用Access的数据库拆分向导（"工具"，"数据库实用工具"，"拆分数据库"）来帮助实现数据库的划分。

5、法人清算系统的应用情况

现该系统在大通证券股份有限公司运行良好，极大减轻了财会人员工作强度，同时各种清晰、完善的数据报表加强了公司对于公司及下属营业部财务的监管力度，堵塞了漏洞。

6、法人清算系统的改进

现系统使用Microsoft Outlook建立及发送邮件，可以改进为通过公司局域网中的邮件服务器发送。