Ecel宏讀取Wincc變量記錄

1、利用 Excel 宏讀取 Wincc 變量記錄生成報(bào)表Dim sPro, sDsn, sSer, sCon, sSqlDim conn, oRs, oComDim DSNNameDim i As Integer Dim sStart, sStop As StringSub DTPFormat()日期控件顯示格式Sheet1.DTPicker1.Format = dtpCustom Sheet1.DTPicker1.CustomFormat = yyyy-MM-dd hh:mm Sheet1.DTPicker1.Value = Now()Sheet1.DTPicker2.Format = dtp

2、Custom Sheet1.DTPicker2.CustomFormat = yyyy-MM-dd hh:mm Sheet1.DTPicker2.Value = Now()End SubSub get_wincc_data()-Get Database DSN nameSet DSNName = CreateObject(CCHMIRuntime.HMIRuntime) sDsn = DSNName.Tags(DatasourceNameRT).Read -build connection stringsPro = Provider=WinCCOLEDBProvider.1; sDsn = C

3、atalog= & sDsn & ; sSer = Data Source=.WinCC sCon = sPro & sDsn & sSerSet conn = CreateObject(ADODB.Connection) conn.ConnectionString = sCon conn.CursorLocation = 3 conn.OpenSet oRs = CreateObject(ADODB.Recordset)Set oCom = CreateObject(ADODB.Command) oCom.CommandType = 1Set oCom.ActiveConnection =

4、conn 查詢啟止時(shí)間sStart = Year(DTPicker1.Value) & - & Month(DTPicker1.Value) & - Day(DTPicker1.Value) & & Hour(DTPicker1.Value) & : & Minute(DTPicker1.Value)sStop = Year(DTPicker2.Value) & - & Month(DTPicker2.Value) & - Day(DTPicker2.Value) & & Hour(DTPicker2.Value) & : & Minute(DTPicker2.Value)轉(zhuǎn)為 UTC 時(shí)間

5、sStart = DateAdd(h, -8, CDate(sStart) sStop = DateAdd(h, -8, CDate(sStop)讀取 Fan1_T1sSql = Tag:R,(ProcessValueArchivestart_flag),& sStart & , & sStop & order by datetimeoCom.CommandText = sSqlSet oRs = oCom.ExecuteIf (oRs.EOF) ThenoRs.CloseElseoRs.MoveFirsti = 0Do While Not oRs.EOFDim start_timestart

6、_time = DateAdd(h, 8, CDate(oRs.fields(1)Sheet1.Cells(i + 3, 2) = start_time oRs.MoveNexti = i + 1Loop oRs.CloseEnd If讀取 Fan1_T2sSql = Tag:R,(ProcessValueArchiveend_flag), & sStart & , & sStop & order by datetimeoCom.CommandText = sSqlSet oRs = oCom.ExecuteIf (oRs.EOF) ThenoRs.CloseElseoRs.MoveFirst

7、i = 0Do While Not oRs.EOFDim end_timeend_time = DateAdd(h, 8, CDate(oRs.fields(1)Sheet1.Cells(i + 3, 3) = end_time oRs.MoveNexti = i + 1Loop oRs.CloseEnd If讀取 Fan1_P1sSql = Tag:R,(ProcessValueArchivereason_flag), & sStart & , & sStop & order by datetimeoCom.CommandText = sSqlSet oRs = oCom.ExecuteIf

8、 (oRs.EOF) ThenoRs.CloseElseoRs.MoveFirsti = 0Do While Not oRs.EOFDim manjuanmanjuan = oRs.fields(2).ValueIf manjuan = 20 ThenSheet1.Cells(i + 3, 4) = 是ElseSheet1.Cells(i + 3, 4) = 否End If oRs.MoveNexti = i + 1Loop oRs.CloseEnd If讀取 Fan1_P2sSql = Tag:R,(ProcessValueArchivereason_flag), & sStart & ,

9、& sStop & order by datetimeoCom.CommandText = sSqlSet oRs = oCom.ExecuteIf (oRs.EOF) ThenoRs.CloseElseoRs.MoveFirsti = 0Do While Not oRs.EOFDim manjuan_flag manjuan_flag = oRs.fields(2).ValueIf manjuan_flag = 10 ThenSheet1.Cells(i + 3, 5) = 停車ElseIf manjuan_flag = 20 ThenSheet1.Cells(i + 5, 5) = 自動切

10、換ElseIf manjuan_flag = 30 ThenSheet1.Cells(i + 3, 5) = 手動切換End IfoRs.MoveNexti = i + 1Loop oRs.CloseEnd If讀取 Fan1_P1sSql = Tag:R,(ProcessValueArchiveall_counter), & sStart & , & sStop & order by datetimeoCom.CommandText = sSqlSet oRs = oCom.ExecuteIf (oRs.EOF) ThenoRs.CloseElseoRs.MoveFirsti = 0Do W

11、hile Not oRs.EOFSheet1.Cells(i + 3, 6) = oRs.fields(2).Value oRs.MoveNexti = i + 1Loop oRs.CloseEnd If讀取 Fan1_P1sSql = Tag:R,(ProcessValueArchiveok_counter), & sStart & , & sStop & order by datetimeoCom.CommandText = sSqlSet oRs = oCom.ExecuteIf (oRs.EOF) ThenoRs.CloseElseoRs.MoveFirsti = 0Do While Not oRs.EOFSheet1.Cells(i + 3, 7) = oRs.fields(2).Value oRs.MoveNexti = i + 1Loop oRs.CloseEnd IfSet oRs = NothingSet conn = NothingEnd SubPrivate Sub DTPicker1_Change()clear_cell 清除已經(jīng)填充的數(shù)據(jù) get_wincc_data 讀取