Option Explicit
Function action

'此为查询按钮中的代码
'变量定义和初始化                                                                                                                                                                                                                                                                 
Dim sPro,sDsn,sSer,sCon,conn,sSql,oRs,oCom
Dim tagDSNName
Dim m
Dim LocalBeginTime, LocalEndTime,UTCBeginTime, UTCEndTime
Dim objExcelApp,objExcelBook,objExcelSheet,sheetname
sheetname="Sheet1"

'打开Excel模板
 Set objExcelApp = CreateObject("Excel.Application")
  objExcelApp.Visible = False
  objExcelApp.Workbooks.Open "G:\生产日报表DEMO.xlsx"
  objExcelApp.Worksheets(sheetname).Activate
  

Dim strBeginTime,strEndTime
objExcelApp.Worksheets(sheetname).cells(2,1).value="日期："&  CStr(Year(Now))&"-"&CStr(Month(Now))&"-"&CStr(Day(Now))
strBeginTime = CStr(Year(Now)) & "-"&CStr(Month(Now)) & "-"&CStr(Day(Now))& " 00:00:00"   'Year(DTPicker1.Value) & "-" & Month(DTPicker1.Value) & "-" & Day(DTPicker1.Value) & " 9:00:00"
strEndTime = CStr(Year(Now)) & "-"&CStr(Month(Now)) & "-"&CStr(Day(Now))& " 23:59:59"   'Year(DTPicker1.Value) & "-" & Month(DTPicker1.Value) & "-" & Day(DTPicker1.Value) & " 10:00:00"
'准备查询条件 Catalog、UTC开始时间、UTC结束时间、时间间隔
 Set tagDSNName = HMIRuntime.Tags("@DatasourceNameRT")
  tagDSNName.Read
  UTCBeginTime = DateAdd("h" ,-8,strBeginTime)
  UTCEndTime= DateAdd("h" ,-8,strEndTime)
  UTCBeginTime = Year(UTCBeginTime) & "-" & Month(UTCBeginTime) & "-" & Day(UTCBeginTime) & " " & Hour(UTCBeginTime) & ":" & Minute(UTCBeginTime) & ":" & Second(UTCBeginTime)
  UTCEndTime = Year(UTCEndTime) & "-" & Month(UTCEndTime) & "-" & Day(UTCEndTime) & " " & Hour(UTCEndTime) & ":" & Minute(UTCEndTime) & ":" & Second(UTCEndTime)
'创建数据库联接
 sPro = "Provider=WinCCOLEDBProvider.1;"
 sDsn = "Catalog=" &tagDSNName.Value& ";"
 sSer = "Data Source=.\WinCC"
 sCon = sPro + sDsn + sSer
 Set conn = CreateObject("ADODB.Connection")
  conn.ConnectionString = sCon
  conn.CursorLocation = 3
  conn.Open
'定义查询的命令文本 SQL
Set oRs = CreateObject("ADODB.Recordset")
 Set oCom = CreateObject("ADODB.Command")
  oCom.CommandType = 1
 Set oCom.ActiveConnection = conn
 
Dim j,tag_number
j = 1
Do While Not (j > 5)

Select Case j
 Case 1
 tag_number = 1
 Case 2
 tag_number = 2
 Case 3
 tag_number = 3
 Case 4
 tag_number = 4
 Case 5
 tag_number = 5
 Case 6
 tag_number = 6
 Case 7
 tag_number = 45
 Case 8
 tag_number = 92
 Case 9
 tag_number = 86
 Case 10
 tag_number = 87
 Case 11
 tag_number = 85
 Case 12
 tag_number = 84
 Case 13
 tag_number = 89
 Case 14
 tag_number = 88
End Select
 sSql = "Tag:R,"& tag_number & ",'" & UTCBeginTime & "','" & UTCEndTime & "' order by datetime"
    oCom.CommandText = sSql
 Set oRs = oCom.Execute
If (oRs.EOF) Then
oRs.Close
Else
oRs.MoveFirst   
Do While Not oRs.EOF                           '是否到记录末尾，循环填写表格  
Dim TIAOJIAN1,TIAOJIAN2         
    TIAOJIAN1 = GetLocalDate(oRs.Fields(1).Value)
    TIAOJIAN2 = Hour(TIAOJIAN1)
 objExcelApp.Worksheets(sheetname).cells(TIAOJIAN2+6,j+1).value= oRs.Fields(2).Value
 oRs.MoveNext
Loop
oRs.Close 
End If
j=j+1
Loop

Set oRs = Nothing
 conn.Close
Set conn = Nothing

Dim patch,filename
 filename=CStr(Year(Now))&"-"&CStr(Month(Now))&"-"&CStr(Day(Now))
 patch= "G:\DAY\生产日报表"&filename&".xlsx"  
 objExcelApp.ActiveWorkbook.SaveAs patch
 objExcelApp.Workbooks.Close
 objExcelApp.Quit
 Set objExcelApp= Nothing
End Function