各位好,本人设置一个归档,通过online trend查看历史归档都是有数据的,通过如下脚本定时去生成xlsx文件时,总是说没有数据,怎么破?
Option Explicit
Function action
'此为查询按钮中的代码
'变量定义和初始化
Dim sPro,sDsn,sSer,sCon,conn,sSql,oRs,oCom
Dim tagDSNName
Dim m,i
Dim LocalBeginTime, LocalEndTime,UTCBeginTime, UTCEndTime,sVal ,UTCTime ,Localtime
Dim objExcelApp,objExcelBook,objExcelSheet,sheetname
On Error Resume Next
sheetname="Sheet1"
'打开Excel模板
Set objExcelApp = CreateObject("Excel.Application")
objExcelApp.Visible = False
objExcelApp.Workbooks.Open "C:\abc.xlsx"
objExcelApp.Worksheets(sheetname).Activate
'准备查询条件 Catalog、UTC开始时间、UTC结束时间、时间间隔
Set tagDSNName = HMIRuntime.Tags("@DatasourceNameRT")
tagDSNName.Read
UTCBeginTime = CStr(Year(Now)) & "-" & CStr(Month(Now)) & "-" & CStr(Day(Now)-2) & " 16:00:00"
UTCEndTime = CStr(Year(Now)) & "-" & CStr(Month(Now)) & "-" & CStr(Day(Now)-1) & " 15:59:59"
'创建数据库联接
sPro = "Provider=WinCCOLEDBProvider.1;"
sDsn = "Catalog=" &tagDSNName.Value& ";"
sSer = "Data Source=CDD-PC\WinCC"
sCon = sPro + sDsn + sSer
Set conn = CreateObject("ADODB.Connection")
conn.ConnectionString = sCon
conn.CursorLocation = 3
conn.Open
'定义查询的命令文本 SQL
sSql = "Tag:R,('PVArchive\a'),'" & UTCBeginTime & "','" & UTCEndTime & "',"
sSql=sSql+"'order by Timestamp ASC'"
MsgBox sSql
Set oRs = CreateObject("ADODB.Recordset")
Set oCom = CreateObject("ADODB.Command")
oCom.CommandType = 1
Set oCom.ActiveConnection = conn
oCom.CommandText = sSql
'填充数据NewPdl0NewPdl0NewPdl0NewPdl0NewPdl0到Excel中
Set oRs = oCom.Execute
m = oRs.RecordCount
If (m > 0) Then
objExcelApp.Worksheets(sheetname).cells(2,1).value=oRs.Fields(1).Name
objExcelApp.Worksheets(sheetname).cells(2,2).value=oRs.Fields(2).Name
oRs.MoveFirst
i=3
Do While Not oRs.EOF '是否到记录末尾,循环填写表格
objExcelApp.Worksheets(sheetname).cells(i,1).value= GetLocalDate(oRs.Fields(1).Value)
objExcelApp.Worksheets(sheetname).cells(i,2).value= oRs.Fields(2).Value
oRs.MoveNext
i=i+1
Loop
oRs.Close
Else
MsgBox "没有所需数据……"
Set oRs = Nothing
conn.Close
Set conn = Nothing
objExcelApp.Workbooks.Close
objExcelApp.Quit
Set objExcelApp= Nothing
Exit Function
End If
'释放资源
Set oRs = Nothing
conn.Close
Set conn = Nothing
'生成新的文件,关闭Excel
Dim patch,filename
filename=CStr(Year(Now))&CStr(Month(Now))&CStr(Day(Now))&CStr(Hour(Now))&CStr(Minute(Now))&CStr(Second(Now))
patch= "c:\elecRecord_"&filename&".xlsx"
objExcelApp.ActiveWorkbook.SaveAs patch
objExcelApp.Workbooks.Close
objExcelApp.Quit
Set objExcelApp= Nothing
MsgBox "成功生成数据文件!"
End Function