Wincc V7.3 vbs 读取多个变量归档数据到excel.doc

WinccV7.3vbs读取多个变量归档数据到excel前面的一篇博客记录了如何读取多个变量归档数据到mshgrid控件，根据的是西门子官网的教学。有网友询问为什么他照着官网方法就是无法导出到excel。我自己也做了一遍，没有问题。本篇主要记录导出按钮的脚本。前面的准备工作与上一篇一致，导出按钮的vbs脚本如下：Sub OnClick(ByVal Item)Dim myCatalog,myDS,PCName,cnstr,sqlstr1,sqlstr2Dim xlapp,BTime,ETime,utcbtime,utcetime,utcbtstr,utcetstrDim conobj,rsobj1,comobj1Dim rsobj2,comobj2Dim rscount,i,curRowDim filenamemyCatalog=HMIRuntime.Tags(DatasourceNameRT).ReadPCName=HMIRuntime.Tags(LocalMachineName).ReadmyDS=PCName & WinccSet BTime=HMIRuntime.Tags(btime)Set ETime=HMIRuntime.Tags(etime)北京时间时区修正utcbtime=Dateadd(h,-8,BTime.Read) 起始时间utcetime=Dateadd(h,-8,ETime.Read) 结束时间日期时间格式修正utcbtstr = Year(utcbtime) & - & Month(utcbtime) & - & Day(utcbtime) & & Hour(utcbtime) & : & Minute(utcbtime) & : & Second(utcbtime)utcetstr = Year(utcetime) & - & Month(utcetime) & - & Day(utcetime) & & Hour(utcetime) & : & Minute(utcetime) & : & Second(utcetime)连接字符串cnstr=Provider=WinCCOLEDBProvider.1; Catalog= & myCatalog & ; Data Source= &myDS创建连接对象Set conobj=CreateObject(ADODB.Connection)conobj.connectionstring=cnstrconobj.CursorLocation = 3conobj.Open查询字符串sqlstr = Tag:R,(VAflow1;VAflow2), & utcbtstr & , & utcetstr & , & order by Timestamp ASC,TimeStep=1,1sqlstr1 = Tag:R,(VAflow1), & utcbtstr & , & utcetstr & , & order by Timestamp ASC,TimeStep=1,1sqlstr2 = Tag:R,(VAflow2), & utcbtstr & , & utcetstr & , & order by Timestamp ASC,TimeStep=1,1进行查询Set rsobj1 = CreateObject(ADODB.Recordset)Set comobj1 = CreateObject(ADODB.Command)comobj1.CommandType = 1Set comobj1.ActiveConnection = conobjcomobj1.CommandText = sqlstr1Set rsobj1 = comobj1.ExecuteSet rsobj2 = CreateObject(ADODB.Recordset)Set comobj2 = CreateObject(ADODB.Command)comobj2.CommandType = 1Set comobj2.ActiveConnection = conobjcomobj2.CommandText = sqlstr2Set rsobj2 = comobj2.Executerscount=rsobj1.recordcountrsobj1.movefirstrsobj2.movefirstif rscount=0 thenmsgbox 没有记录exit subend ifSet xlapp=CreateObject(Excel.Application)xlapp.visible=Falsexlapp.workbooks.add初始化excelxlapp.worksheets(1).cells(1,1)=编号：xlapp.worksheets(1).cells(1,2)=QB-2017.001xlapp.worksheets(1).range(a2:c2).mergecells=True 合并单元格xlapp.worksheets(1).cells(2,1)=这是一个测试xlapp.worksheets(1).cells(2,1).HorizontalAlignment = 3 文字居中xlapp.worksheets(1).cells(3,1)=日期时间xlapp.worksheets(1).cells(3,2)=flow1xlapp.worksheets(1).cells(3,3)=flow2导出到excelFor i=1 To rscountxlapp.worksheets(1).cells(3+i,1)=Dateadd(h,+8,rsobj1.fields(1).value)xlapp.worksheets(1).cells(3+i,2)=rsobj1.fields(2).valuexlapp.worksheets(1).cells(3+i,3)=rsobj2.fields(2).valuersobj1.movenextrsobj2.movenextNext释放资源Set rsobj1 = NothingSet rsobj2 = Nothingconobj.CloseSet conobj = Nothing画边框xlapp.worksheets(1).range(a3:c & CStr(3+rscount).borders(1).linestyle=9xlapp.worksheets(1).range(a3:c & CStr(2+rscount).borders(1).weight=2xlapp.worksheets(1).range(a3:c & CStr(2+rscount).borders(2).linestyle=9xlapp.worksheets(1).range(a3:c & CStr(2+rscount).borders(2).weight=2xlapp.worksheets(1).range(a3:c & CStr(2+rscount).borders(3).linestyle=9xlapp.worksheets(1).range(a3:c & CStr(2+rscount).borders(3).weight=2xlapp.worksheets(1).range(a3:c & CStr(2+rscount).borders(4).linestyle=9xlapp.worksheets(1).range(a3:c & CStr(2+rscount).borders(4).weight=2保存文件filename= c: & Year(Now) & 年 & Month(Now) & 月 & Day(Now) & 日- & Hour(Now)