證券公司法人清算系統解決方案

摘 要：本文以證券公司法人清算業(yè)務(wù)為背景，把多個(gè)Office應用程序的特性集成到一個(gè)解決方案中，從而提高和促進(jìn)了用戶(hù)的生產(chǎn)效率。

關(guān)鍵詞：Access、Excel、Outlook、Automation、VBA、DAO

1、引言

每一個(gè)Microsoft Office應用程序都包含了一組旨在幫助開(kāi)發(fā)人員完成一組相關(guān)任務(wù)的強大工具。例如：Microsoft Access提供了強大的數據管理以及查詢(xún)能力;Microsoft Excel提供了數學(xué)分析以及報告工具;Microsoft Outlook提供了發(fā)送和接受電子函件、計劃、合同以及任務(wù)管理；而Microsoft Word支持用戶(hù)創(chuàng )建和維護文檔，跟蹤不同用戶(hù)間的文檔版本、創(chuàng )建窗體和模板等。以上應用使單個(gè)Office應用程序具備可編程能力以及使得創(chuàng )建集成Office解決方案成為可能的關(guān)鍵技術(shù)是稱(chēng)之為Automation的組件對象模型(COM)技術(shù)。Automation允許開(kāi)發(fā)人員使用Visual Basic for Application(VBA)代碼創(chuàng )建和控制由支持相應編程接口的應用程序、動(dòng)態(tài)鏈接庫、ActiveX控件提供的軟件對象。VBA和Automation支持編寫(xiě)獨立的Office應用程序。例如：可以在A(yíng)ccess中創(chuàng )建一個(gè)隱藏的Excel實(shí)例，用它對Access數據完成數學(xué)運算和分析操作。

2、法人清算系統概述

上海證券中央登記結算公司在中央交收體制下實(shí)行會(huì )員法人結算制度。上海證券交易所的會(huì )員單位均以法人名義在登記公司開(kāi)立結算帳戶(hù)，統一辦理其所有下屬機構在上海證券交易所進(jìn)行的證券交易的清算交收業(yè)務(wù)。

證券公司（法人）財務(wù)部每天上午通過(guò)滬、深法人專(zhuān)用平臺撥號上網(wǎng)接收法人清算數據文件。清算文件包含有該公司下屬交易席位各項交易費用明細。需要分類(lèi)匯總：先統計出各席位各項費用，再統計出各營(yíng)業(yè)部費用，最后匯總出公司各項費用。對賬、打印、傳真給下屬營(yíng)業(yè)部，供其對賬。根據統計費用，劃撥金額，以保障公司備付金。

現在證券公司通過(guò)兼并重組，規模日趨龐大，下屬營(yíng)業(yè)部少則十幾家，多則幾十，甚至上百家，所以上述流程由人工處理完成已越來(lái)越無(wú)法勝任。

綜合運用Office應用程序，上述工作得以快捷、準確完成，使以前幾個(gè)人花費近一上午的工作在十幾分鐘就可以完成。

法人清算系統業(yè)務(wù)流程見(jiàn)附圖。

3、法人清算系統的設計結構

系統主要由三部分組成：準備數據、處理數據、發(fā)送數據。

3.1 準備數據

滬深清算文件為壓縮文件，接收完畢后由系統自動(dòng)完成更名、解壓，生成DBF文件，同時(shí)完成數據備份工作。

3.2 處理數據

系統主要部分，選用Access為主程序，功能包括：

● 數據導入，生成當日所需明細數據表 ● 當日劃撥 ● 各類(lèi)明細、匯總報表 ● 生成下屬營(yíng)業(yè)部所需數據文件，為Excel文件 ● 生成郵件，準備發(fā)往各營(yíng)業(yè)部

對于開(kāi)發(fā)運行小型商務(wù)系統的應用程序來(lái)說(shuō)，Access是一個(gè)較好的平臺，它的向導有助于開(kāi)發(fā)人員輕松地快速構造出應用程序的框架；由于它支持代碼模塊，因此，開(kāi)發(fā)人員很容易建立可重用函數的代碼庫；又由于它支持在窗體和報表之后添加代碼，因此它具有強大的窗體和報表自定義功能。 之所以選用Excel文件為下發(fā)文件格式，因為下發(fā)數據包括深、滬明細數據和各類(lèi)匯總數據，可作為工作簿中各工作表，格式清晰，且各營(yíng)業(yè)部可再次利用Excel工具處理，得到想要各類(lèi)數據。

3.3 發(fā)送數據

啟動(dòng)Outlook，發(fā)送郵件。

4、法人清算系統的關(guān)鍵技術(shù)

4.1 用代碼導入外部數據

Access的DoCmd對象提供了三種導入外部數據的方法，它們是： TransferDatabase、TransferText和TransferSpreadsheet 下面的示例使用DoCmd對象的TransferDatabase方法從數據庫導入數據： Sub ImportDatabase() DoCmd.TransferDatabase _ TransferType:=acImport，_ '執行轉換的類(lèi)型 DatabaseType:="dBase III"，_ '導入數據庫的類(lèi)型 DatabaseName:=APPPATH，_'數據庫的名稱(chēng) ObjectType:=actable，_'導入對象的類(lèi)型 Source:="Customer"，_'導入源對象的名稱(chēng) Destination:="tblCustomer"，_'導入目標對象的名稱(chēng) StructureOnly:=False '只導入表的結構，還是結構、數據都導入 End sub

4.2 數據訪(fǎng)問(wèn)技術(shù)

本系統選用DAO(Data Access Objects)技術(shù)。

DAO代碼可以創(chuàng )建、修改和刪除Jet引擎對象，使用它可以完成以下工作：

● 分析已存在的數據庫結構

● 增加或修改表和查詢(xún)

● 創(chuàng )建新數據庫

● 通過(guò)修改查詢(xún)所基于的SQL來(lái)改變查詢(xún)定義

● 遍歷記錄集

● 修改表數據

4.3 展示數據

在Office解決方案中有許多不同的展示數據的方法，如何建構報表依賴(lài)于解決方案是如何設計的以及數據存儲在什么地方。本系統解決方案以Access為基礎，用戶(hù)可以通過(guò)Access窗體和數據訪(fǎng)問(wèn)頁(yè)面操作數據；同時(shí)若需要在Excel中觀(guān)察和操作數據，以便以最大的靈活性來(lái)創(chuàng )建定制報表，可以把數據從Access中導入到Excel。

在A(yíng)ccess運行的代碼里使用Automation創(chuàng )建Excel的查詢(xún)表QueryTable或PivotTable報表。使用Automation可以使用戶(hù)對從數據庫到Excel轉換數據的過(guò)程進(jìn)行更多的控制，還可以使用Automation來(lái)格式化報表，過(guò)濾數據，創(chuàng )建統計圖等。用戶(hù)能夠在A(yíng)ccess中簡(jiǎn)單地單擊一個(gè)按鈕，就得到Excel中已格式化的報表。

下面的示例創(chuàng )建一個(gè)新的數據庫查詢(xún)并把數據返回到工作表，建立動(dòng)態(tài)報表。

要在VBA中創(chuàng )建一個(gè)新的查詢(xún)表，使用QueryTables集合的Add方法。QueryTables集合從屬于Worksheet對象，并且包含了該工作表的所有QueryTable對象。創(chuàng )建了一個(gè)新的查詢(xún)表后，必須使用QueryTable對象的Refresh方法在查詢(xún)表中顯示數據。 Dim dbReset As Database Dim rstReset As Recordset Dim wksNew As Excel.Worksheet Dim qtbData As Excel.QueryTable ...... Set rstReset = dbReset.OpenRecordset("營(yíng)業(yè)部") 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的對象模型：

● 編寫(xiě)在本地工程文件或與Outlook本地安裝相關(guān)聯(lián)的COM加載項中運行的VBA代碼。

● 使用Outlook窗體中自帶的腳本環(huán)境，該窗體用于顯示諸如消息和約會(huì )這樣的項目。

● 通過(guò)Automation在其它Office應用程序或支持VBA的其它應用程序中使用Outlook。

本系統使用第三種方式。在使用VBA訪(fǎng)問(wèn)Outlook對象、方法、屬性之前，必須首先單擊Visual Basic編輯器的Tools菜單項下的Reference來(lái)設置對Microsoft Outlook對象庫的引用。

下面的示例InitializeOutlook過(guò)程創(chuàng )建一個(gè)新的、隱藏的Outlook實(shí)例；CreateMail過(guò)程創(chuàng )建一個(gè)郵件消息，設置收件人、附件、主題和消息內容，然后發(fā)送郵件。

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 劃分數據庫對象

把包含表的數據庫稱(chēng)為表數據庫，而包含其它對象的數據庫稱(chēng)為應用程序數據庫。通過(guò)從應用程序數據庫鏈接到表數據庫，就把這兩個(gè)數據庫連接起來(lái)了。采用這種策略的理由是：

● 可維護性

● 性能

● 可擴展性

如果已經(jīng)設計了應用程序，并且在同樣的數據庫中包括了所有的表和其他數據庫對象，可以使用Access的數據庫拆分向導（"工具"，"數據庫實(shí)用工具"，"拆分數據庫"）來(lái)幫助實(shí)現數據庫的劃分。

5、法人清算系統的應用情況

現該系統在大通證券股份有限公司運行良好，極大減輕了財會(huì )人員工作強度，同時(shí)各種清晰、完善的數據報表加強了公司對于公司及下屬營(yíng)業(yè)部財務(wù)的監管力度，堵塞了漏洞。

6、法人清算系統的改進(jìn)

現系統使用Microsoft Outlook建立及發(fā)送郵件，可以改進(jìn)為通過(guò)公司局域網(wǎng)中的郵件服務(wù)器發(fā)送。