VBA报表（精选3篇）
VBA报表 篇1
1、引言
报表功能几乎是所有财务系统的通用需求, 因为用户往往需要用直观、综合的方式来获取数据库的信息, 而不仅仅是查看单个基表的数据信息;其次, 报表还是决策者关心的内容, 报表传递是数据信息在不同管理层次之间流动的一种通用方式。
一个通用财务报表系统不但应该具有报表定义简单灵活, 而且能实现各种复杂形式的报表、能处理表内表间的逻辑关系, 还应具有电子表格功能, 能提供存储、编辑、预览和打印等功能。
2、设计思想
一张财务报表的外观格式由表头、表尾、页眉、页脚、底纹、字体等因素组成, 要想制作一张万能的财务报表完全满足用户对上述因素的需求是不太可能的。但这些报表都有一个共同的特点, 即他们对同一类报表因素是固定不变或基本相同的, 由此, 作者提出一种分层设计的思想, 将报表外观格式与数据定义分开, 报表外观格式的变动不影响报表数据。即认为一张财务报表由外观格式和数据组成, 外观格式可以看作是模板, 数据是按照用户需求从数据库中选取和运算得到的。这样, 财务报表系统就可以分成三个层次:模板层、中间层、数据层。其中, 模板层的主要功能是实现报表外观格式的管理。数据层的主要功能是实现数据的管理, 包括数据的定义、数据操纵等。中间层的主要功能是实现将用户的需求转化为报表数据, 包括需求定义、选取数据和添加数据。
设计的关键在中间层, 首先要将用户的需求转化为报表参数, 然后根据参数逐步解释执行得到报表的每一项数据, 最后根据报表数据内容修改外观格式。
3、系统设计
3.1 工具选择
Excel是Office的主要应用程序之一, 是微软公司新推出的一个功能强大的电子表格应用软件, 具有强大的数据计算与分析处理功能, 可以把数据用表格及各种图表的形式表现出来, 使制作出来的表格图文并茂, 信息表达更清晰。Excel可以针对单元格进行操作, 使财务报表的外观格式效果更加具体, 另外Office是目前应用最广泛的办公软件之一, 一般的财务人员对Excel比较熟悉, 利于系统的应用。
VBA是一种宏语言, 主要能用来扩展Office的应用程式功能, 有助于使工作自动化, 提高工作效率。另外, 由于VBA可以直接应用Office套装软件的各项强大功能, 可以简化模板的使用, 可以对数据进行复杂的操作和分析, 所以对于程序设计人员的程序设计和开发更加方便快捷。
3.2 设计流程
(1) 用户根据财务报表的类型决定使用何种模板, Excel中自带了部分模板, 用户可以在这些模板的基础上进行修改, 也可以重新制作满足用户需求的模板。如图1是修改过的损益报表模板。
(2) 用户根据财务报表的具体内容生成报表参数, 报表参数可以是文本字符、用户输入的数据、查询语句、函数及四则表达式、报表内置公式等, 系统根据具体参数类型确定数据来源及添加的列位置, 文本字符和用户输入的数据要保存其原数据;函数和公式的区域参数要用asc码转换为字符, 如表示cells (2, 1) 的单元格为A2, 应该表示为Chr (64+1) &"2", 同样, 表示cells (i, j) 的单元格应该表示为Chr (64+j) &str (i) , 列数大于26的需另外计算;如果报表参数是查询语句, 需要连接数据库, 从数据库中读取数据。
(3) 以行为单位添加数据到报表中, 按照报表参数类型:文本字符和用户输入的数据、查询语句、函数和公式, 采用不同方式添加数据。
(4) 对添加完数据的报表外观样式进行适当调整。
3.3 关键技术
3.3.1 连接数据库
3.3.2 根据查询语句读取报表数据
Dim adoRec As New adodb.Recordset
adoRec.Open strsql, cnn'strsql为报表参数提供的查询语句
3.3.3 根据报表参数添加报表数据
Do While Not adoRec.EOF
With uExcel.ActiveSheet
'省去for循环语句和case判断语句, for循环语句的作用是顺序定位报表数据的列位置;case判断语句的作用是判断当前列位置的报表参数类型, 对不同类型的报表参数添加方式不同。
.Cells (intRow, intCol) .Value=adoRec.Fields (inta (intl) ) .Value
'inta () 是数组, 存放当前列对应的字段索引。
'上述语句是从数据库中读取数据的添加, 函数语句可以看作由函数头, 区域参数, 函数尾组成。区域参数的生成前面已有说明, 在此不再赘述。
End With
intRow=intRow+1
adoRec.MoveNext
Loop
3.3.4 分页处理
4、结束语
本文提出了分层设计思想, 利用EXCEL制作报表的强大功能以及VBA可以对数据进行复杂的操作和分析的优点, 实现了财务报表的处理。虽然在中间层的操作上比较复杂, 但对于不太复杂的报表来说, 是比较容易处理的。本系统投入运行以来, 效果理想, 表明将分层设计思想应用到财务报表系统中是切实可行的。
参考文献
[1].刘纪敏.基于ActiveX控件与SQL查询的通用报表软件设计[J].计算机应用与软件, 2005, vol.22.
[2].金敏等.利用VBA在EXCEL下开发工业实时控制系统中的智能报表[J].计算机工程与应用, 1999, 7.
[3].田金兰等.EXCEL环境下的报表自动生成工具[J].小型微型计算机系统, 1996, vol.4.
[4].张大方, 邬娇萍.基于Windows通用财务报表系统的设计与实现[J].计算机工程与应用, 1998, 1.
[5].崔彦锋等.基于XML与Office的电站报表开发[J].工业控制计算机, 2005年18卷第9期.
用VBA实现煤炭采样系统报表 篇2
在PLC——计算机监控系统中,界面设计通常由组态软件来实现。RSView32组态软件提供了强大的数据监控能力,方便用户进行上位机的画面组态。但该软件并没有提供强大的报表功能,一旦涉及到复杂的数据处理,组态软件往往表现的力不从心。但是RSView32集成了微软的VBA作为脚本语言编辑器。可以随意定制开发后台应用程序。只要使用VBA进行简单的编程,运行Microsoft Office的应用程序,就可以实现大多数报表功能。
1 RSView32组态软件简介
RSView32是罗克韦尔公司开发的组态软件,它是集成的、组件化的人机接口,广泛地应用于监视和控制自动化设备和过程。它可以方便的与AB PLC进行无缝的结合,也可以通过使用OPC或DDE的通讯方式与其他公司的设备进行连接。
RSView32提供如下功能:
1)使用直接嵌入到图形显示中的可利用和定制的ActiveX控件,方便用户创建形象的监控画面以及动画演示;
2)对象模型外露,用户和其他软件产品可访问系统的接口部分,保证系统的对外兼容性;
集成微软的VBA作为内建的程序设计语言,方便用户开发系统应用程序;
3)支持OPC标准,并且可作为OPC服务器或者客户端,利用该技术可以与多种设备进行快速可靠的通讯。
2 工程项目概述
煤炭采制样电气控制系统项目的主要任务是煤炭样品的采样以及采样过程总体监视与管理系统。系统采用美国AB公司的PLC双机热备,中控室与各PLC通过工业以太网进行数据通讯。特殊设备驱动采用变频器控制。设置塔顶分站、塔底分站、MCC分站和中控室分站,通过工业级现场总线进行数据连接。各分站既能够独立进行现场数据采集和控制,又能够通过网络实现数据共享。同时亦能够通过网络间的数据传送,将每个站点的仪表及泵阀等状态信息传送到上位机,完成中央控制室的实时监控。各PLC现场子站上均配置现场操作员终端,作为现场操作员界面,对现场工艺进行实时显示,同时能够对参数进行设置。
采制样系统的三台初级采样器BM10、BM11和BM12安装于输煤皮带机的皮带转接处,对应着主皮带机。操作者输入采样标准和批量后,控制系统按照国标归定公式计算取样间隔。
初采器BM10和BM12分别对应1、2号进样皮带。BM11下设初采翻板门,运行时,根据BM10、BM12的运行情况选择左翻或右翻。当一号皮带机运行时,由BM10、BM11采样;当二号皮带机运行时,由BM11、BM12采样;两条皮带同时工作时,BM10、BM12采样。
整个采制样系统由进样皮带、磁分离器、一级缩分器、缩分翻板门、破碎机、二级缩分器、转运皮带、样品收集器、弃煤皮带、弃煤翻板门、料斗仓等设备组成两套完全独立的运行系统。
3 报表的实现
该煤炭采制样系统要求自动生成装船采样报告。报告内容包括:船名、装船日期、煤炭种类、国别、批量、采样单元数、子样数、样品罐号、开装时间和装毕时间等。这些数据主要分为三类,第一类直接读取PLC数据,由系统自动采集;第二类由操作人员在装船开始时从人机界面输入,如船名、国家、煤炭种类等,这类数据和第一类一起,定时存入数据库;第三类数据在采样完毕后得到,如样品称重。需要建立一个窗口,以便在每个批量采样结束后添加记录。
本项目以Microsoft Office Access作为数据库,利用RSView32的VB编辑器完成采集数据的工作。在Access中完成报表的查询、显示和打印。
3.1 自动添加记录
首先需要创建Microsoft Office Access数据库,并在里面创建批量表和单元数据表,每个表包含各自需要记录的所有字段。批量表和单元数据表中均包含报验号字段,便于之后查询索引。
当采样单元开始和完成时,由PLC发送脉冲信号,系统自动记录采样开始和完成时间。同时在单元采样完成时,自动向Access的单元数据表中添加记录。批量数据以同样的方式记录,在VBA编辑器中添加代码:
3.2 手工补充数据
最终样品量需要在全部采制样完成后,由操作人员手工称重得出,因此记录表中该项在最后添加。利用RSView32内嵌的VB编辑器制作样品称重添加窗体。利用下拉框选择对应的报验号、系统号和单元号,锁定一条已有的记录,手工输入称重值。
3.3 查询及打印
在Access中创建查询窗体,分别选择查询范围的起始和终止时间进行查询操作:
结合VBA编程利用Access的查询、窗体和报表格式编辑功能生成所需报表。以时间为查询条件,报验号为检索条件,每张表单列示一个报验号下面的所有采样信息。在报表格式的设计上,将批量表与采样单元数据表进行嵌套。以便在查询结果中显示每个批量采样下的各个单元信息。表格上部显示装船批量信息;中部显示该批量下所有采制样单元信息;下部预留操作人员需手工填写的信息位置。最终表单如图2所示。
最后在RSView32的VB编辑器中添加调用Access的命令,并在画面中组态按钮,直接运行该命令即可。
此外,本项目还建立了采样阶段查询报表,和故障查询报表。创建方法与上述类似,故不赘述。
4 结束语
Microsoft Office Access具有灵活的报表处理功能。与其在RSView32中用VBA代码间接的进行报表处理,不如直接在Access中进行处理,然后用RSView32命令调用更便捷。本项目中将RSView32采集的数据通过VBA存储到数据库Microsoft Access中,并根据具体需要编制相应的程序生成表单形式的数据报表。这种方式可以作为低成本的报表实现方法,方便了操作与生产。
参考文献
[1]苗苗,朱秀慧.PLC工控机与集散控制系统在组态软件RSView32中用VBA实现报表[Z].2006.
[2]王学军.基于RSView32组态软件的数据库报表设计与研究[J].电脑知识与技术,2007.
[3]石红瑞,孙洪涛,郭利进.用Visual Basic实现RSView32的报表转换和打印[J].石油化工自动化,2003.
VBA报表 篇3
财务人员日常工作中经常要填制、报送各种各样的数据报表, 尤其要定期向外报送会计报表。Microsoft Excel由于其强大的数据处理和分析功能被广泛应用在财务领域。但是对于大多数的财务人员来说, 对Microsoft Excel还只是停留在简单的应用上, 极少有人利用编程来开发具有一定复杂性和针对性的应用系统。本文重点介绍利用Excel的VBA与ADO和SQL技术相结合自动生成财务报表。本文结合笔者会计工作中编报基本建设单位会计报表实例, 利用用友ERP-U8总账系统输出财务数据的功能, 借助Excel的VBA与ADO和SQL技术相结合, 实现会计报表自动生成。
2 会计报表自动生成模块的设计与实现
VBA是Visual Basic for Applications的缩写, 是新一代的标准宏语言, 是基于Visual Basic for Windows发展起来的。ADO是Active Data Objects的缩写, 又被称为OLE自动化接口, 是Microsoft通用的数据访问技术。SQL (Structured Query Language, 结构化查询语言) , 是一种数据库查询和程序设计语言, 用于存取数据以及查询、更新和管理关系数据库系统。本文主要以资金平衡表的自动生成为例进行阐述。
2.1 设计思路
(1) 从用友ERP-U8总账系统中输出“发生额及余额表”, 输出文件类型选择为Microsoft Excel 97-2000。此表作为自动生成会计报表的数据源。
(2) 按照《国有建设单位会计制度》, 建立资金平衡表报表项目与会计科目的对照关系。并将用友总账系统导出的“发生额及余额表”与报表项目与会计科目对照关系统表存放在同一Excel工作簿中, 取名“数据源.xls”。
(3) 建立资金平衡表模板, 作为数据输出目标。
(4) 按照资金平衡表报表项目与会计科目的对照关系, 从“发生额及余额表”查询到相关数据填到资金平衡表模板中。
2.2 VBA实现代码
打开Excel报表模板, 在“工具”菜单中, 指向宏, 然后单击“Visual Basic编辑器” (或者简单地按下ALT+F11组合键) 。在“Visual Basic编辑器”中的“插入”菜单中, 添加“模块”。输入代码实现。
模块代码
本代码的原理 (以报表项目“器材”实现过程为例) :如图1数据源之对照关系表所示, 报表中“器材”项目应该从“211, 213, 214, 215, 216, 219, 271”等会计科目 (科目编码中间用半角逗号分隔) 取数, 相关科目期初、期末数据求和后, 分别填入模板“C19” (期初数) 和“D19” (期末数) 单元格。代码13至38行是利用VBA的循环流程, 使记录集rs1指针定位到项目“器材”, 通过rs1.Fields ("科目编码") 语句将“器材”项目要从哪些会计科目取值的信息传递给“类1”处理, 然后“类1”将结果返回22代码行, 生成一条SQL查询语句。代码行24为执行该查询语句, 并将查询结果返回给记录集rs2。代码行25至36行为将rs2查询结果输出到给定资金平衡表模板。类1主要实现两大功能:一是将数据源之对照关系表中用半角逗号分隔的“科目编码”去除逗号分拆成每一个单独的科目编码;二是将分拆后的科目编码组合为SQL语句的Where条件。类1的实现代码如下:
此外, 因为外部数据库是Excel工作簿, 为了使程序能够正常进行, 需要在VBA中建立对ADO对象库Microsoft Active XData Objects 2.X Library的引用 (其中2.X表示的是版本号) 。若是Office 2007版本, Provider="Microsoft.ACE.OLEDB.12.0"。
3结语
本文以资金平衡表为实例, 利用Excel VBA结合ADO和SQL语句进行复杂条件下的数据库查询, 把数据库数据与Exce有机结合, 实现会计报表的自动生成。实现报表自动生成的关键是建立会计科目与报表项目的对照关系并按照该对照关系实现数据查询汇总的程序代码 (类1的代码) 。通过重新定义对照关系, 该报表自动生成实现方式可以进一步推广应用, 实现自动生成各种类型会计报表。
摘要：本文以资金平衡表为实例, 介绍了利用Excel VBA结合ADO和SQL语句进行复杂条件下的数据库查询, 把数据库数据与Excel有机结合, 实现会计报表自动生成的思路和方法。