如何在组态软件RSView32中用VBA实现报表

罗克韦尔RSVIEW32、SE万能报表开发经验1引言随着罗克韦尔工业自动化软件在自动化行业应用日益广泛，工控最终客户对报表的要求也越来越详细，越人性化。

在这篇文章以前，已经有很多工程师、教授发表了通过rsview32和rsview se自带的vba来实现报表功能，但要求开发工程师对vb有一些基本的了解，用起来复杂，不具有通用、易用性。

本文根据实例来演示自己开发的vba万用报表，使读者可以直接上手，不再为报表设计而发愁。

2应用实例分析本文以rsview 32做演示(rsview se道理一样)。

首先我们要确定将数据记录存到硬盘哪个分区，例如，我想将数据存储到d盘aaa文件夹下，那么我们现在d 盘建立一个aaa文件夹，并在文件夹中建立我们的excel数据模板，并命名为model：(如图1所示)图1excel数据模板模板建立好以后需要设置所需的内容，大家可以根据自己需要任意设置，下面是举例的设置：(如图2所示)图2内容设置模板建立完毕后保存一下，就可以关上模板。

其次把rsview32的vba打开，也是最关键的一步，然后黏贴万能代码,看起来代码很复杂，其实只需要修改3步，即可：sub excel()dim xlapp, xlbook, xlsheet as objectset xlapp = createobject("excel.application")set xlbook = xlapp.workbooks.open("d:\aaa\model.xls")这里修改成我们所需的存储存储路径，也是刚才建立模板的路径set xlsheet = xlbook.worksheets("sheet1")这里对应的是excel中的名称(如图3所示)如果默认，无需修改图3 excel中的名称dim ai as stringai = format(now, "yyyy-mm-dd hh-mm")set a = gtagdb.gettag("zsh\pt120")set b = gtagdb.gettag("zsh\pt130")set d = gtagdb.gettag("zsh\ta")set e = gtagdb.gettag("zsh\tt120")set f = gtagdb.gettag("zsh\tt130")set g = gtagdb.gettag("zsh\tt121")set h = gtagdb.gettag("zsh\tt131")set i = gtagdb.gettag("zsh\tt001")set l = gtagdb.gettag("zsh\t1")set m = gtagdb.gettag("zsh\t2")set n = gtagdb.gettag("zsh\t3")set o = gtagdb.gettag("zsh\t4")set p = gtagdb.gettag("zsh\t5")set c = gtagdb.gettag("zsh\at100")set k = gtagdb.gettag("zsh\ljxfl")上面所述的set *= gtagdb.gettag(*******)是将报表需要的数据建立起来，例如：set a = gtagdb.gettag("zsh\pt120")这句的意思是需要的a数据来源于rsview32标记数据库下zsh文件夹下的pt120标签。

利用VBA在组态软件中实现生产报表编写
曾鸣
【期刊名称】《《可编程控制器与工厂自动化(PLC FA)》》
【年(卷),期】2008(000)011
【摘要】本文介绍了美国Rockwell公司RSView32组态软件,以及利用其内嵌的VBA开发报表的方法。

通过两个例子说明了在VBA中,调用全局变量访问实时数据及通过ADO方式访问历史数据,并生成报表的过程。

【总页数】3页(P69-71)
【作者】曾鸣
【作者单位】深圳水务集团有限公司
【正文语种】中文
【中图分类】TP393
【相关文献】
1.组态软件RSView32中用VBA实现报表的方法 [J], 苗苗;朱秀慧;王海
2.利用VBA编写Excel报表通用汇总程序 [J], 江育奇
3.利用VBA编程实现生产日报表的自动生成 [J], 杨廷福;王会梅
4.在组态软件RSView32中用VBA实现报表 [J], 苗苗; 朱秀慧; 王海
5.利用工程组态软件的VBA程序调用Excel报表及Access数据库 [J], 杨文川;贾瑜华;王磊
因版权原因，仅展示原文概要，查看原文内容请购买。

1 引言在PLC——计算机监控系统中，界面设计通常组态软件来实现。

作为组态软件，罗克韦尔RSView32提供了强大的数据监控能力，方便用户进行上位机的画面组态。

由于RSView32并没有提供强大的报表功能，如果只进行简单的数据分析，报表变化不是很复杂，并且对数据可移动性无太高要求，组态软件可实现。

然而一旦涉及到复杂的数据处理，组态软件往往表现得力不从心。

但是RSView32内嵌有VBA，VBA是Visual Basic for Application的简写，它以VB语言为基础，经过修改并运行在Microsoft Office的应用程序。

只要使用VBA进行简单的编程便可以实现大多数报表功能了。

2 开发环境及数据记录格式简介RSView32是RockWell SoftWare公司的组态软件，它提供集成的、组态化的人机接口，广泛的用来监视和控制自动化设备和过程。

它除了可以方便的与ALLEN -BRADLEY PLC进行无缝的结合应用，也可以通过使用OPC的通讯方式与其它公司的设备进行连接。

ODBC(Open Database Connectivity开放式数据库互连)是由微软推出的工业标准，一种开放的独立于厂商的API应用程序接口，可以跨平台访问各种个人计算机、小型机以及主机系统。

ODBC 作为一个工业标准，绝大多数数据库厂商都为自己的产品提供了ODBC接口或提供了ODBC支持，这其中就包括常用的SQL SERVER、ORACLE、INFORMIX等，当然也包括了Access。

RSView32的数据记录是一个在特定条件下保存指定的标记数值或数据的过程。

在“数据记录设置”编辑器里,可以创建定义不同条件的数据记录模式,本文将数据通过ODBC接口存储到数据库Access里,即将标记值被记录到用ODBC接口连接到的数据库Access的三个表格里:标记表格(TagTable):把标记名存储到一个索引里;符点数表格(FloatTable):存储模拟量和开关量标记数值;字符串表格(StringTable):存储字符串数值。

RSViewSE软件用VBA做报表【第三章】前文提过，在RSViewSE里面做报表，有一种高级玩法，完全不依赖RSViewSE软件，而是通过RSLinx软件编写程序将PLC内的数据直接读取回来，按照一定的时间规律记录到数据库内，这个过程是不需要显示出来的，所以这个程序只需要默默的在后台执行就可以了。

当然这个程序可以使用VB、C#、C++等高级语言编写。

其实在工控领域，很多软件支持VBA和VBS脚本，这两种语言都跟VB有很大关系，VB也算是一种上了年纪的编程语言了，微软目前也已经停止了对它的更新，但这不妨碍VB在工程领域内的应用。

王老湿学习VB还是跟年龄有关系了。

当然，市面上目前已经有逼格高的组态软件把脚本支持到了JS和Python了，看到这两种语言好像一下子让我想到了90后00后，算了，先搞VB吧。

本文主要介绍如何在VB内编写程序实现从AB的PLC内读取数据并记录到数据库。

跟前面一样，我们依然使用ADODB这种方式在vb内与数据库建立连接，此次我们使用ODBC的替代者和继承者OLEDB来作为统一的数据库连接工具。

OK，闲言少叙，直接开干。

这是一个比较骚的操作，其运行将完全与RSViewSE软件隔离开，数据记录到数据库后再编写程序读取出来，全程都不依赖于组态软件。

本次实验我们使用SQL Server数据库来记录需要的报表变量。

场景模拟：跟以前一样，假设有个水厂，要做一些生产报表的数据，数据包括所有泵站的日供水量，耗电量，运行时间，月供水量，耗电量，运行时间等数据。

要求每天记录一次。

1、新建个RSLogix5000的项目，在其内部创建一些模拟报表的数据。

在Logix5000里面新建变量数组，创建泵站编号命名的数组，使用DINT数据类型，我们先创建50个元素的数组，每个数组的具体内容定义在在Logix5000的程序内完成，创建数组的目的也是在编程读取的时候方便做循环语句。

将其下载到PLC内，本文档使用模拟器模拟PLC，下载完之后直接运行项目。

RSView32 的资料很少，关于VBA的资料就少之又少，找到一点VBA的程序代码，给那些刚接触的朋友们参考下！Option ExplicitPrivate oTag As TagPrivate WithEvents m_oActivity As ActivityDim oconn As ADODB.ConnectionDim ors As ADODB.RecordsetDim oExcel As ObjectDim oBook As ObjectDim oSheet As ObjectDim sselect As StringDim sselect1 As StringPrivate WithEvents timer2 As ccrpTimerDim wait1, quit1 As StringSub weekth()On Error GoTo Error1Set timer2 = New ccrpTimertimer2.EventType = TimerPeriodictimer2.Interval = 1000timer2.Stats.Frequency = 65timer2.Enabled = TrueExit SubError1:MsgBox Err.Description, vbOKOnly + vbQuestionEnd SubPrivate Sub Timer2_Timer(ByVal Milliseconds As Long)On Error GoTo Error1Dim reportday As DateDim week_of_year, day_of_week1 As IntegerDim a1 As TagIf gTagDb.GetTag("report_date").Value = 0 Then gTagDb.GetTag("report_date").Value = 1If gTagDb.GetTag("report_month").Value = 0 Then gTagDb.GetTag("report_month").Value = 1If gTagDb.GetTag("report_month").Value = 1 Or gTagDb.GetTag("report_month").Value = 3 OrgTagDb.GetTag("report_month").Value = 5 Or gTagDb.GetTag("report_month").Value = 7 Or gTagDb.GetTag("report_month").Value = 8 Or gTagDb.GetTag("report_month").Value = 10 Or gTagDb.GetTag("report_month").Value = 12 ThenIf gTagDb.GetTag("report_date").Value >= 32 ThengTagDb.GetTag("report_date").Value = 31End IfEnd IfIf gTagDb.GetTag("report_month").Value = 4 Or gTagDb.GetTag("report_month").Value = 6 Or gTagDb.GetTag("report_month").Value = 9 Or gTagDb.GetTag("report_month").Value = 11 Then If gTagDb.GetTag("report_date").Value >= 31 ThengTagDb.GetTag("report_date").Value = 30End IfEnd IfIf gTagDb.GetTag("report_month").Value = 2 And (gTagDb.GetTag("report_year").Value Mod 4 = 0) ThenIf gTagDb.GetTag("report_date").Value >= 30 ThengTagDb.GetTag("report_date").Value = 29End IfEnd IfIf gTagDb.GetTag("report_month").Value = 2 And (gTagDb.GetTag("report_year").Value Mod 4 <> 0) ThenIf gTagDb.GetTag("report_date").Value >= 29 ThengTagDb.GetTag("report_date").Value = 28End IfEnd Ifweek_of_year = DatePart("ww", DateValue((gTagDb.GetTag("report_year").Value) & "-" & (gTagDb.GetTag("report_month").Value) & "-" & (gTagDb.GetTag("report_date").Value))) gTagDb.GetTag("report_week").Value = week_of_yearday_of_week1 = DatePart("w", DateValue((gTagDb.GetTag("report_year").Value) & "-" & (gTagDb.GetTag("report_month").Value) & "-" & (gTagDb.GetTag("report_date").Value))) gTagDb.GetTag("report_dayofweek").Value = day_of_week1Exit SubError1:MsgBox Err.Description, vbOKOnly + vbQuestionEnd SubSub GetProjectPath()'This sub gets the current projects actual pathOn Error GoTo Error_HandlerDim Tpath As TagSet Tpath = gTagDb.GetTag("TemplatePath")Tpath = """" & gProject.PathExit SubError_Handler:'Log Error to the Activity LoggActivity.Log "Activity_EventsStop() failed: " & Err.Description & " (ErrNo= " & _Err.number & ")", roActivityError, , , "Activity_EventsStop()"End SubSub plc1_system_clock()On Error GoTo ErrHandlerDim system_year, system_month, system_date, system_hour, system_min, system_second As TagDim set_year, set_month, set_date, set_hour, set_min, set_second As TagSet system_year = gTagDb("System\year")Set system_month = gTagDb("System\month")Set system_date = gTagDb("System\dayofmonth")Set system_hour = gTagDb("System\hour")Set system_min = gTagDb("System\minute")Set system_second = gTagDb("System\second")Set set_year = gTagDb("plc1\datetime_set\0")Set set_month = gTagDb("plc1\datetime_set\1")Set set_date = gTagDb("plc1\datetime_set\2")Set set_hour = gTagDb("plc1\datetime_set\3")Set set_min = gTagDb("plc1\datetime_set\4")Set set_second = gTagDb("plc1\datetime_set\5")set_year.Value = system_year.Valueset_month.Value = system_month.Valueset_date.Value = system_date.Valueset_hour.Value = system_hour.Valueset_min.Value = system_min.Valueset_second.Value = system_second.Value'Release resourcesSet system_month = NothingSet system_date = NothingSet system_hour = NothingSet system_min = NothingSet system_second = NothingSet set_year = NothingSet set_month = NothingSet set_date = NothingSet set_hour = NothingSet set_min = NothingSet set_second = NothingExit SubErrHandler:Select Case Err.numberCase roErrorTagValueCommError, roErrorTagEventgActivity.Log Err.Description, roActivityError, roActivityTagRead'Release resourcesSet system_year = NothingSet system_month = NothingSet system_date = NothingSet system_hour = NothingSet system_min = NothingSet system_second = NothingSet set_year = NothingSet set_month = NothingSet set_date = NothingSet set_hour = NothingSet set_min = NothingSet set_second = NothingExit SubCase roErrorTagValueUninitialized'Release resourcesSet system_year = NothingSet system_month = NothingSet system_date = NothingSet system_hour = NothingSet system_second = NothingSet set_year = NothingSet set_month = NothingSet set_date = NothingSet set_hour = NothingSet set_min = NothingSet set_second = NothingExit SubCase roErrorTagValueStaleResume NextCase ElsegActivity.Log Err.DescriptionResume NextEnd SelectEnd SubSub plc2_system_clock()On Error GoTo ErrHandlerDim system_year, system_month, system_date, system_hour, system_min, system_second As TagDim set_year, set_month, set_date, set_hour, set_min, set_second As TagSet system_year = gTagDb("System\year")Set system_month = gTagDb("System\month")Set system_date = gTagDb("System\dayofmonth")Set system_hour = gTagDb("System\hour")Set system_min = gTagDb("System\minute")Set system_second = gTagDb("System\second")Set set_year = gTagDb("plc2\datetime_set\0")Set set_month = gTagDb("plc2\datetime_set\1")Set set_date = gTagDb("plc2\datetime_set\2")Set set_hour = gTagDb("plc2\datetime_set\3")Set set_min = gTagDb("plc2\datetime_set\4")Set set_second = gTagDb("plc2\datetime_set\5")set_year.Value = system_year.Valueset_month.Value = system_month.Valueset_date.Value = system_date.Valueset_hour.Value = system_hour.Valueset_min.Value = system_min.Valueset_second.Value = system_second.Value'Release resourcesSet system_year = NothingSet system_month = NothingSet system_date = NothingSet system_hour = NothingSet system_min = NothingSet system_second = NothingSet set_year = NothingSet set_month = NothingSet set_date = NothingSet set_hour = NothingSet set_min = NothingSet set_second = NothingExit SubErrHandler:Select Case Err.numberCase roErrorTagValueCommError, roErrorTagEventgActivity.Log Err.Description, roActivityError, roActivityTagRead'Release resourcesSet system_year = NothingSet system_month = NothingSet system_date = NothingSet system_hour = NothingSet system_min = NothingSet system_second = NothingSet set_year = NothingSet set_month = NothingSet set_date = NothingSet set_hour = NothingSet set_min = NothingSet set_second = NothingExit SubCase roErrorTagValueUninitialized'Release resourcesSet system_year = NothingSet system_month = NothingSet system_date = NothingSet system_hour = NothingSet system_min = NothingSet system_second = NothingSet set_year = NothingSet set_month = NothingSet set_date = NothingSet set_hour = NothingSet set_min = NothingSet set_second = NothingExit SubCase roErrorTagValueStaleResume NextCase ElsegActivity.Log Err.DescriptionResume NextEnd SelectEnd SubSub Download_Validation(Source As String, Destination As String)Dim SourceTag As TagDim DestinationTag As TagDim CmdString As StringOn Error GoTo Err_Handle1 '如果有错误就转到Err_Handle1子程序中Set SourceTag = gTagDb.GetTag(Source)Set DestinationTag = gTagDb.GetTag(Destination)If SourceTag.Value <= DestinationTag.MaximumValue And SourceTag.Value >= DestinationTag.MinimumValue ThenDestinationTag.Value = SourceTag.ValueDestinationTag.WriteConfigurationElseCmdString = "Display ValidateFailed /T" & DestinationgCommand.Execute (CmdString)End IfExit SubErr_Handle1:'Log error to the Activity LoggActivity.Log "Download_Validation() failed: " & Err.Description & " (ErrNo= " & _ Err.number & ")", roActivityError, , , "Download_Validation()" End SubSub Activity_EventsStart()'This sub is run to start Activity event processing.On Error GoTo Error_Handler'Create the Activity and Tag objectsSet m_oActivity = gActivitySet oTag = gTagDb.GetTag("ActivityBarMessage")Exit SubError_Handler:'Log Error to the Activity LoggActivity.Log "Activity_EventsStart() failed: " & Err.Description & " (ErrNo= " & _ Err.number & ")", roActivityError, , , "Activity_EventsStart()"End SubSub Activity_EventsStop()'End Sub'This sub is run to stop Activity events and close the text fileOn Error GoTo Error_Handler'Destroy the Activity and Tag objectsSet m_oActivity = NothingSet oTag = NothingExit SubError_Handler:'Log Error to the Activity LoggActivity.Log "Activity_EventsStop() failed: " & Err.Description & " (ErrNo= " & _ Err.number & ")", roActivityError, , , "Activity_EventsStop()"End SubPrivate Sub m_oActivity_LoggedToActivityBar(ByVal Message As String, ByVal ActivityType As Long)'This sub is run every time a message is sent to the Activity Bar'NOTE: Because logging an error to the Activity Log will fire this event again,' errors are ignored.On Error Resume NextDim MyPos As Integer'Set the maximum message length to 82 characters, length of a string tagMessage = Left(Message, 82)'Set the string tag = to the message logged to Activity BaroTag.Value = Message'Does activity bar message contain "does not have security" ? Any non-zero number means it does 'Start at position 1 and preform a non case sensitive text comparisonMyPos = InStr(1, Message, "访问这个命令的权限")'If the error message contains the above string then display popupIf (ActivityType = 1) And (MyPos <> 0) ThengCommand.Execute ("display Security_Warning")End IfMyPos = InStr(1, Message, "图形:登录用户没有访问这个图形的权限")'If the error message contains the above string then display popupIf (ActivityType = 0) And (MyPos <> 0) ThengCommand.Execute ("display Security_Warning")End IfMyPos = InStr(1, Message, "没有权利")'If the error message contains the above string then display popupIf (ActivityType = 0) And (MyPos <> 0) ThengCommand.Execute ("display Security_Warning")End IfEnd SubSub water_quality_daily_report()On Error GoTo Error1Dim datefrom, dateto As VariantDim rc, rc_daily, rc1 As IntegerDim max1 As VariantDim max(80, 80), datetime(80, 80) As VariantDim max_1(80, 80), min_1(80, 80), avg_1(80, 80), sum_1(80, 80), tagindex1(80, 80) As Double Dim j, para As LongDim date_print As Integerwait1 = "Display wait"quit1 = "abort wait"gCommand.Execute (wait1)date_print = gTagDb.GetTag("w_date_print").ValueIf date_print = 1 Thendatefrom = DateV alue(Now() - 1)Elsedatefrom = DateV alue((gTagDb.GetTag("report_year").Value) & "-" & (gTagDb.GetTag("report_month").Value) & "-" & (gTagDb.GetTag("report_date").Value))End Ifdateto = DateV alue(datefrom + 1)Set oconn = New ADODB.ConnectionSet ors = New ADODB.Recordsetoconn.CursorLocation = adUseClientFor para = 0 To 40sselect1 = "Select dateandtime,Val,TagIndex from FloatTable where DateAndTime>=#" & datefrom & "# and DateAndTime<#" & dateto & "# and TagIndex= " & para & " and marker=chr(83)"oconn.Open "DSN=report"ors.Open sselect1, oconn, adOpenStatic, adLockBatchOptimisticrc = ors.recordcountIf rc < 1 ThenIf date_print = 0 ThenMsgBox (datefrom & "to " & dateto & "没有数据")End Ifors.Closeoconn.CloseSet ors = NothingSet oconn = NothinggCommand.Execute (quit1)Exit SubEnd Ifmax1 = ors.GetRowsFor j = 0 To rc - 1max(para, j) = val(max1(1, j))datetime(para, j) = max1(0, j)Next jmax_1(para, 0) = max(para, 0)min_1(para, 0) = max(para, 0)sum_1(para, 0) = max(para, 0)For j = 1 To rc - 1sum_1(para, 0) = sum_1(para, 0) + max(para, j)If max(para, j) >= max_1(para, 0) Then max_1(para, 0) = max(para, j)If max(para, j) <= min_1(para, 0) Then min_1(para, 0) = max(para, j)Next javg_1(para, 0) = sum_1(para, 0) / rcors.Closeoconn.CloseNext paraSet ors = NothingSet oconn = Nothing'EXCELSet oExcel = New Excel.ApplicationSet oBook = oExcel.Workbooks.Open("d:\xinjiang_HMI\report\" + "XXXXXXX日报表.xls")Set oSheet = oBook.Worksheets("XXXXXXX日报表")With oSheet.Range("B6:o29").NumberFormatLocal = "0.00_ ".Range("B37:o60").NumberFormatLocal = "0.00_ ".Range("B68:o91").NumberFormatLocal = "0.00_ ".Range("B6:o29").HorizontalAlignment = xlCenter.Range("B37:o60").HorizontalAlignment = xlCenter.Range("B68:o91").HorizontalAlignment = xlCenter.Range("B6:o29").Font.Size = 10.Range("B37:o60").Font.Size = 10.Range("B68:o91").Font.Size = 10For para = 0 To 12For j = 0 To rc - 1.Cells(6 + j, 2 + para).Value = max(para, j).Cells(6 + j, 1).Value = datetime(0, j)Next j' .Cells(31, 2 + para).Value = avg_1(para, 0)Next paraFor para = 13 To 26For j = 0 To rc - 1.Cells(37 + j, 2 + para - 13).Value = max(para, j).Cells(37 + j, 1).Value = datetime(0, j)Next j' .Cells(31, 2 + para-13).Value = avg_1(para, 0)Next paraFor para = 27 To 40For j = 0 To rc - 1.Cells(68 + j, 2 + para - 27).Value = max(para, j).Cells(68 + j, 1).Value = datetime(0, j)Next j' .Cells(31, 2 + para-27).Value = avg_1(para, 0)Next para.Range("A6:A29").NumberFormatLocal = "h:mm;@".Range("A37:A60").NumberFormatLocal = "h:mm;@".Range("A68:A91").NumberFormatLocal = "h:mm;@"If date_print = 1 Then.Range("A3").FormulaR1C1 = "日期:" & gTagDb.GetTag("system\year").Value & "年" & gTagDb.GetTag("system\month").Value & "月" & gTagDb.GetTag("system\dayofmonth").Value - 1 & "日".Range("A34").FormulaR1C1 = "日期:" & gTagDb.GetTag("system\year").Value & "年" & gTagDb.GetTag("system\month").Value & "月" & gTagDb.GetTag("system\dayofmonth").Value - 1 & "日".Range("A65").FormulaR1C1 = "日期:" & gTagDb.GetTag("system\year").Value & "年" & gTagDb.GetTag("system\month").Value & "月"& gTagDb.GetTag("system\dayofmonth").Value - 1 & "日"Else.Range("A3").FormulaR1C1 = "日期:" & gTagDb.GetTag("report_year").Value & "年" & gTagDb.GetTag("report_month").Value & "月" & gTagDb.GetTag("report_date").Value & "日".Range("A34").FormulaR1C1 = "日期:" & gTagDb.GetTag("report_year").Value & "年" & gTagDb.GetTag("report_month").Value & "月" & gTagDb.GetTag("report_date").Value & "日".Range("A65").FormulaR1C1 = "日期:" & gTagDb.GetTag("report_year").Value & "年" & gTagDb.GetTag("report_month").Value & "月" & gTagDb.GetTag("report_date").Value & "日"End If.Range("a6:o29").Sort , key1:=.Range("a6").Range("a37:o60").Sort , key1:=.Range("a37").Range("a68:o91").Sort , key1:=.Range("a68")', header:=xlNoEnd WithIf date_print = 1 ThenoBook.PrintOutoExcel.Application.DisplayAlerts = FalseoExcel.QuitoExcel.Application.DisplayAlerts = TrueEnd IfgCommand.Execute (quit1)If date_print = 0 ThenWith oBook.Application.Visible = True.Windows("XXXXXXX日报表.xls").Visible = TrueEnd WithEnd IfSet oSheet = NothingSet oBook = NothingSet oExcel = NothingExit SubError1:'MsgBox Err.Description, vbOKOnly + vbQuestiongCommand.Execute (quit1)End SubSub water_quality_monthly_report()On Error GoTo Error1Dim month1, year1, date1, monthcount, para As IntegerDim datecount, maxdate As IntegerDim datefrom, dateto As VariantDim month_print As Integerwait1 = "Display wait"quit1 = "abort wait"gCommand.Execute (wait1)month_print = gTagDb.GetTag("w_month_print").ValueIf month_print = 1 Thenmonth1 = gTagDb.GetTag("system\month").Value - 1Elseyear1 = gTagDb.GetTag("report_year").Valuemonth1 = gTagDb.GetTag("report_month").Valuedate1 = gTagDb.GetTag("report_date").ValueEnd If'1\3\5\7\8\10\12 month 31 dateIf month1 = 1 Or month1 = 3 Or month1 = 5 Or month1 = 7 Or month1 = 8 Or month1 = 10 Or month1 = 12 Thenmaxdate = 31End If'4\6\9\11 month 30 dateIf month1 = 4 Or month1 = 6 Or month1 = 9 Or month1 = 11 Thenmaxdate = 30End If'2 month 29 dateIf month1 = 2 And (year1 Mod 4 = 0) Thenmaxdate = 29End If'2 month 28 dateIf month1 = 2 And (year1 Mod 4 <> 0) Thenmaxdate = 28End IfDim rc, rc_daily, rc1 As IntegerDim max1 As VariantDim max(80, 80), datetime(80, 80) As VariantDim max_1(80, 80), min_1(80, 80), avg_1(80, 80), sum_1(80, 80) As DoubleDim avg_2(80, 80), sum_2(80, 80) As DoubleDim j, k As Longk = 0For datecount = 1 To maxdateIf month_print = 1 Thendatefrom = DateV alue((gTagDb.GetTag("system\year").Value) & "-" & (gTagDb.GetTag("system\month").Value - 1) & "-" & datecount)Elsedatefrom = DateV alue((gTagDb.GetTag("report_year").Value) & "-" & (gTagDb.GetTag("report_month").Value) & "-" & datecount)End Ifdateto = DateV alue(datefrom + 1)Set oconn = New ADODB.ConnectionSet ors = New ADODB.Recordsetoconn.CursorLocation = adUseClientFor para = 0 To 40sselect1 = "Select dateandtime,Val,TagIndex from FloatTable where DateAndTime>=#" & datefrom & "# and DateAndTime<#" & dateto & "# and TagIndex= " & para & "and marker=chr(83)"oconn.Open "DSN=report"ors.Open sselect1, oconn, adOpenStatic, adLockBatchOptimisticrc = ors.recordcountIf rc < 1 ThenIf month_print = 0 ThenMsgBox (datefrom & " to " & dateto & "没有数据!")End Ifors.Closeoconn.CloseSet ors = NothingSet oconn = NothingGoTo next1End Ifmax1 = ors.GetRowsFor j = 0 To rc - 1max(para, j) = val(max1(1, j))datetime(para, j) = max1(0, j)Next jmax_1(para, datecount) = max(para, 0)min_1(para, datecount) = max(para, 0)sum_1(para, datecount) = max(para, 0)For j = 1 To rc - 1sum_1(para, datecount) = sum_1(para, datecount) + max(para, j)If max(para, j) >= max_1(para, datecount) Then max_1(para, datecount) = max(para, j)If max(para, j) <= min_1(para, datecount) Then min_1(para, datecount) = max(para, j)Next javg_1(para, datecount) = sum_1(para, datecount) / rcors.Closeoconn.Closesum_2(para, 0) = sum_2(para, 0) + avg_1(para, datecount)Next paraSet ors = NothingSet oconn = Nothingk = k + 1next1:Next datecountIf k = 0 Then GoTo next2'EXCELSet oExcel = New Excel.ApplicationSet oBook = oExcel.Workbooks.Open("d:\xinjiang_HMI\report\" + "XXXXXXX月报表.xls")Set oSheet = oBook.Worksheets("XXXXXXX月报表")With oSheet.Range("B6:o36").NumberFormatLocal = "0.00_ ".Range("B44:o74").NumberFormatLocal = "0.00_ ".Range("B84:o115").NumberFormatLocal = "0.00_ ".Range("B6:o36").HorizontalAlignment = xlCenter.Range("B44:o74").HorizontalAlignment = xlCenter.Range("B84:o115").HorizontalAlignment = xlCenter.Range("B6:o36").Font.Size = 10.Range("B44:o74").Font.Size = 10.Range("B84:o115").Font.Size = 10For datecount = 1 To maxdateFor para = 0 To 12.Cells(5 + datecount, 1).Value = datecount.Cells(5 + datecount, 2 + para).Value = avg_1(para, datecount)'.Range("A6:A36").NumberFormatLocal = "0_ "Next paraFor para = 13 To 26.Cells(43 + datecount, 1).Value = datecount.Cells(43 + datecount, 2 + para - 13).Value = avg_1(para, datecount)'.Range("A6:A36").NumberFormatLocal = "0_ "Next paraFor para = 27 To 40.Cells(83 + datecount, 1).Value = datecount.Cells(83 + datecount, 2 + para - 27).Value = avg_1(para, datecount)'.Range("A6:A36").NumberFormatLocal = "0_ "Next paraNext datecountIf month_print = 1 Then.Range("A3").FormulaR1C1 = "日期:" & gTagDb.GetTag("system\year").Value & "年" & gTagDb.GetTag("system\month").Value - 1 & "月".Range("A41").FormulaR1C1 = "日期:" & gTagDb.GetTag("system\year").Value & "年" & gTagDb.GetTag("system\month").Value - 1 & "月".Range("A81").FormulaR1C1 = "日期:" & gTagDb.GetTag("system\year").Value & "年" & gTagDb.GetTag("system\month").Value - 1 & "月"Else.Range("A3").FormulaR1C1 = "日期:" & gTagDb.GetTag("report_year").Value & "年" & gTagDb.GetTag("report_month").Value & "月".Range("A41").FormulaR1C1 = "日期:" & gTagDb.GetTag("report_year").Value & "年" & gTagDb.GetTag("report_month").Value & "月".Range("A81").FormulaR1C1 = "日期:" & gTagDb.GetTag("report_year").Value & "年" & gTagDb.GetTag("report_month").Value & "月"End IfEnd With'dim para1 As Integer'For para1 = 0 To 12'avg_2(para1, 0) = sum_2(para1, 0) / k'' With oSheet' .Cells(37, 2 + para1).V alue = avg_2(para1, 0)' End With'Next para1'For para1 = 13 To 26'avg_2(para1, 0) = sum_2(para1, 0) / k' With oSheet'.Cells(75, 2 + para1 - 13).V alue = avg_2(para1, 0) ' End With'Next para1'For para1 = 27 To 40'avg_2(para1, 0) = sum_2(para1, 0) / k' With oSheet'.Cells(115, 2 + para1 - 27).Value = avg_2(para1, 0) ' End With'Next para1next2:If k = 0 ThenIf month_print = 0 ThenMsgBox "这个月没有数据"End IfgCommand.Execute (quit1)Exit SubElseIf month_print = 1 ThenoBook.PrintOutoExcel.Application.DisplayAlerts = False oExcel.QuitoExcel.Application.DisplayAlerts = TrueEnd IfgCommand.Execute (quit1)If month_print = 0 ThenWith oBook.Application.Visible = True.Windows("XXXXXXX月报表").Visible = True End WithEnd IfEnd IfSet oSheet = NothingSet oBook = NothingSet oExcel = NothingExit SubError1:'MsgBox Err.Description, vbOKOnly + vbQuestiongCommand.Execute (quit1)End SubSub water_quality_weekly_report()On Error GoTo Error1Dim month1, year1, date1, week1, monthcount, para, datecount1, maxdate As IntegerDim datefrom, dateto, datefrom1 As Variantwait1 = "Display wait"quit1 = "abort wait"gCommand.Execute (wait1)year1 = gTagDb.GetTag("report_year").Valuemonth1 = gTagDb.GetTag("report_month").Valuedate1 = gTagDb.GetTag("report_date").Valueweek1 = gTagDb.GetTag("report_week").ValueDim rc, rc_daily, rc1 As IntegerDim max1 As VariantDim max(80, 80), datetime(80, 80) As VariantDim max_1(80, 80), min_1(80, 80), avg_1(80, 80), sum_1(80, 80) As DoubleDim avg_2(80, 80), sum_2(80, 80) As DoubleDim j, k As LongDim day_of_week As IntegerDim week_print As Integerweek_print = gTagDb.GetTag("w_week_print").ValueIf week_print = 1 Thenday_of_week = DatePart("w", DateValue((gTagDb.GetTag("system\year").Value) & "-" & (gTagDb.GetTag("system\month").Value) & "-" & (gTagDb.GetTag("system\dayofmonth").Value) - 7))Elseday_of_week = DatePart("w", DateValue((gTagDb.GetTag("report_year").Value) & "-" & (gTagDb.GetTag("report_month").Value) & "-" & (gTagDb.GetTag("report_date").Value)))End IfIf week_print = 1 ThenIf day_of_week = 1 Then datefrom = DateV alue((gTagDb.GetTag("system\year").Value) & "-" & (gTagDb.GetTag("system\month").Value) & "-" & (gTagDb.GetTag("system\dayofmonth").Value)- 7)If day_of_week = 2 Then datefrom = DateValue(DateValue((gTagDb.GetTag("system\year").Value) & "-" & (gTagDb.GetTag("system\month").Value) & "-" & (gTagDb.GetTag("system\dayofmonth").Value) - 7) - 1)If day_of_week = 3 Then datefrom = DateValue(DateValue((gTagDb.GetTag("system\year").Value) & "-" & (gTagDb.GetTag("system\month").Value) & "-" & (gTagDb.GetTag("system\dayofmonth").Value) - 7) - 2)If day_of_week = 4 Then datefrom = DateValue(DateValue((gTagDb.GetTag("system\year").Value) & "-" & (gTagDb.GetTag("system\month").Value) & "-" & (gTagDb.GetTag("system\dayofmonth").Value) - 7) - 3)If day_of_week = 5 Then datefrom = DateValue(DateValue((gTagDb.GetTag("system\year").Value) & "-" & (gTagDb.GetTag("system\month").Value) & "-" & (gTagDb.GetTag("system\dayofmonth").Value) - 7) - 4)If day_of_week = 6 Then datefrom = DateValue(DateValue((gTagDb.GetTag("system\year").Value) & "-" & (gTagDb.GetTag("system\month").Value) & "-" & (gTagDb.GetTag("system\dayofmonth").Value) - 7) - 5)If day_of_week = 7 Then datefrom = DateValue(DateValue((gTagDb.GetTag("system\year").Value) & "-" & (gTagDb.GetTag("system\month").Value) & "-" & (gTagDb.GetTag("system\dayofmonth").Value) - 7) - 6)ElseIf day_of_week = 1 Then datefrom = DateValue((gTagDb.GetTag("report_year").Value) & "-" & (gTagDb.GetTag("report_month").Value) & "-" & (gTagDb.GetTag("report_date").Value))If day_of_week = 2 Then datefrom = DateValue(DateValue((gTagDb.GetTag("report_year").Value) & "-" & (gTagDb.GetTag("report_month").Value) & "-" & (gTagDb.GetTag("report_date").Value)) - 1)If day_of_week = 3 Then datefrom = DateValue(DateValue((gTagDb.GetTag("report_year").Value) & "-" & (gTagDb.GetTag("report_month").Value) & "-" & (gTagDb.GetTag("report_date").Value)) - 2)If day_of_week = 4 Then datefrom = DateValue(DateValue((gTagDb.GetTag("report_year").Value) & "-" & (gTagDb.GetTag("report_month").Value) & "-" & (gTagDb.GetTag("report_date").Value)) - 3)If day_of_week = 5 Then datefrom = DateValue(DateValue((gTagDb.GetTag("report_year").Value) & "-" & (gTagDb.GetTag("report_month").Value) & "-" & (gTagDb.GetTag("report_date").Value)) - 4)If day_of_week = 6 Then datefrom = DateValue(DateValue((gTagDb.GetTag("report_year").Value) & "-" & (gTagDb.GetTag("report_month").Value) & "-" & (gTagDb.GetTag("report_date").Value)) - 5)If day_of_week = 7 Then datefrom = DateValue(DateValue((gTagDb.GetTag("report_year").Value)。

Rsview 32 报表的步骤1：用VBA 将数据写到Acess中。

代码Sub DateRepWrite()Dim sTagName As StringDim tag1 As TagDim myYear As IntegerDim myMonth As IntegerDim myDay As IntegerDim myTime1 As DateDim myTime2 As DateDim value1 As Double, value2 As Double, value3 As Double, value4 As Double Dim sWell(0 To 10) As StringDim sUnit1(1 To 6) As StringDim sCol(0 To 13) As StringDim sUnit2(1 To 10) As StringDim sCol2(1 To 7) As StringDim sPump(1 To 6) As StringDim wkDates As WorkspaceDim dbDates As DatabaseDim rsDates As RecordsetDim mydate As DateDim sDBFile As StringDim sQuery As StringDim nRow As IntegerDim nCol As IntegerDim sgValue As SingleOn Error GoTo ErrHandler‘TagsScanOn1RepStatus = 1Set tag1 = gTagDb("system\Year")myYear = tag1.ValueSet tag1 = gTagDb("system\Month")myMonth = tag1.ValueSet tag1 = gTagDb("system\Day")myDay = tag1.ValueSet tag1 = gTagDb("system\Date")myTime1 = tag1.ValueSet tag1 = gTagDb("system\Time")myTime2 = tag1.Valuemydate = NowsWell(0) = "Report2\"sWell(1) = "Report1\"sCol(1) = "Test1"sCol(2) = "Test2"sCol(3) = "Test3"sDBFile = gProject.Path & "\VBA\yzwater2.mdb"Set wkDates = Workspaces(0)Set dbDates = wkDates.OpenDatabase(sDBFile)‘向表well_date1添加记录Set rsDates = dbDates.OpenRecordset("SELECT * FROM well_date1") rsDates.MoveLastFor nRow = 0 To 1rsDates.AddNewrsDates.Fields(0).Value = mydatersDates.Fields(1).Value = nRowFor nCol = 1 To 3sTagName = sWell(nRow) & sCol(nCol)Set tag1 = gTagDb(sTagName)rsDates.Fields(nCol).Value = tag1.ValueNext nColrsDates.UpdateNext nRowrsDates.CloseExit SubErrHandler:gActivity.Log "RSView32 VBA Error" & Err.Number & ":" & Err.Description, roActivityErrorResume NextEnd Sub2：利用ADO datacombo datagrid 控件进行查询和显示。

关于rsview32和SE中报表制作的总结SE中报表可以使用VBAExc命令来直接调用vba代码，而SE中没有可以直接调用vba代码的命令，由于vba是实际那出发机制，需要使用画面、显示会对象的加载、变化、单击等事件来触发vba代码的执行。

写报表的步骤日报1.每天0:00生成空表（使用filecopy复制模板文件）。

2.每天每个小时第一分钟读取标签数据库的标签数据，填入当天的报表中。

注：将所有的日报表放在一张工作薄里面，省去写报表是重复执行不同的“打开-写-保存-关闭”操作，不容易出错。

月报1.每月第一天0:00生成空表（使用filecopy复制模板文件）。

2.每天23:01读取当天报表中的汇总数据（平均值）填入月报表对应的天中。

年报1.每年一月1号0:00生成空表（使用filecopy复制模板文件）。

2.每月最后一天23:01读取当月报表中的汇总数据（平均值）填入年报表对应的月中。

以上报表也可以使用第三方数据库存储运行时的实时数据，需要时再自动生成报表或者手动查询生成报表。

使用filecopy来复制模板比“打开模板-另存为报表”的效率更高，而且不会因为误动作导致修改模板内容及格式。

查看报表的步骤1.选择报表类型（日报，月报，年报）2.选择日期3.查询，可以显示在webbrower控件中（office 2003及以下版本）或者直接打开excel表格。

Rsview32读取数据标签，常用方法有以下2种：第一种：使用gtagdb对象的gettag方法例如：With msexcel.Worksheets(2).Cells(i, 2).Value =Gtagdb.gettag("plc1\Fine\LET202\LEVEL").value但该方法读取大量标签时比较慢，不推荐。

第二种：使用tags标签组对象，结合gTagDb对象QueryForTags方法，快速扫描标签值Dim Plc1 As tags‘将plc1文件夹下面的模拟量标签全部包含到plc1标签组中Set Plc1 = gTagDb.QueryForTags("plc1\*", roIncludeAnalog)‘快速扫描plc1文件夹下标签的值Plc1.ScanOn roWaitWith msexcel.Worksheets(2).Cells(1, 2).Value = Plc1.item("plc1\Fine\LET202\LEVEL").Value '1#细格栅液位差End with‘使用完plc1标签组内的标签值后推出扫描plc1Plc1.ScanOff该方法读取大量标签时比较快。