查询用户归档数据到Excel(已测)

《查询用户归档数据到Excel(已测)》由会员上传分享，免费在线阅读，更多相关内容在行业资料-天天文库。

1、'此为查询按钮中的代码'变量定义和初始化DimsPro,sDsn,sSer,sCon,conn,sSql,oRs,oComDimtagDSNNameDimm,iDimLocalBeginTime,LocalEndTime,UTCBeginTime,UTCEndTime,sValDimobjExcelApp,objExcelBook,objExcelSheet,sheetnameitem.Enabled=FalseOnErrorResumeNextsheetname="Sheet1"'打开Excel模板SetobjExcelApp=CreateOb

2、ject("Excel.Application")objExcelApp.Visible=FalseobjExcelApp.Workbooks.Open"D:WinCCWriteExcelabc.xls"objExcelApp.Worksheets(sheetname).Activate'准备查询条件Catalog、UTC开始时间、UTC结束时间、时间间隔SettagDSNName=HMIRuntime.Tags("@DatasourceNameRT")tagDSNName.ReadSetLocalBeginTime=HMIRuntime.T

3、ags("strBeginTime")LocalBeginTime.ReadSetLocalEndTime=HMIRuntime.Tags("strEndTime")LocalEndTime.ReadUTCBeginTime=DateAdd("h",-8,LocalBeginTime.Value)UTCEndTime=DateAdd("h",-8,LocalEndTime.Value)UTCBeginTime=Year(UTCBeginTime)&"-"&Month(UTCBeginTime)&"-"&Day(UTCBeginTime)&""&H

4、our(UTCBeginTime)&":"&Minute(UTCBeginTime)&":"&Second(UTCBeginTime)UTCEndTime=Year(UTCEndTime)&"-"&Month(UTCEndTime)&"-"&Day(UTCEndTime)&""&Hour(UTCEndTime)&":"&Minute(UTCEndTime)&":"&Second(UTCEndTime)HMIRuntime.Trace"UTCBeginTime:"&UTCBeginTime&vbCrLfHMIRuntime.Trace"UTCend

5、Time:"&UTCEndTime&vbCrLfSetsVal=HMIRuntime.Tags("sVal")sVal.Read'创建数据库联接'Provider=MSDASQL.1;PersistSecurityInfo=False;DataSource=CC_ColdMill_11_10_20_15_37_45R;Mode=Read;InitialCatalog=CC_ColdMill_11_10_20_15_37_45RsPro="Provider=MSDASQL.1;"'"Provider=WinCCOLEDBProvider.1;"sD

6、sn="Catalog="&tagDSNName.Value&";"sSer="DataSource=CC_ColdMill_11_10_20_15_37_45R"sCon=sPro+sDsn+sSerSetconn=CreateObject("ADODB.Connection")conn.ConnectionString=sConconn.CursorLocation=3conn.Open'定义查询的命令文本SQL'sSql="Tag:R,('PVArchiveNewTag'),'"&UTCBeginTime&"','"&UTCEndTime

7、&"'"'sSql="Tag:R,('PVArchiveNewTag'),'0000-00-0000:10:00.000','0000-00-0000:00:00.000'"'sSql="Tag:R,('PVArchiveNewTag';'PVArchiveNewTag_1'),'"&UTCBeginTime&"','"&UTCEndTime&"',"'sSql="Tag:R,('PVArchiveNewTag'),'"&UTCBeginTime&"','"&UTCEndTime&"','orderbyTimestampDESC','Ti

8、meStep="&sVal.Value&",1"'sSql="Tag:R,('MillDataArchive速度'),'"&UTCBe