代碼寫在EXCEL宏命令中，用VB開發，部分代碼如下：
Dim sPro, sDsn, sSer, sCon, sSql
Dim conn, oRs, oCom
Dim DSNName
Dim i As Integer
Dim sStart, sStop As String
Sub get_wincc_data()
Dim M_Row, M_Col, M_Hour, m_i As Integer
Dim M_S As String
Dim M, N, Key As Integer
Dim Min As Long
Dim Max As Long
Dim TT As Integer
sDsn = "CC_FS_13_11_20_10_29_04R" '資料庫地址
sPro = "Provider=WinCCOLEDBProvider.1;"
sDsn = "Catalog=" & sDsn & ";"
sSer = "Data source=OS3\WinCC" '本地IP
sCon = sPro & sDsn & sSer
Set conn = CreateObject("ADODB.Connection")
conn.ConnectionString = sCon
conn.CursorLocation = 3
conn.Open
Set oRs = CreateObject("ADODB.Recordset")
Set oCom = CreateObject("ADODB.Command")
oCom.CommandType = 1
Set oCom.ActiveConnection = conn
'查詢啟止時間
sStart = Year(DTPicker1.Value) & "-" & Month(DTPicker1.Value) & "-" & Day(DTPicker1.Value) & " 07:30:00"
M_S = Year(DTPicker1.Value) & "-" & Month(DTPicker1.Value) & "-" & Day(DTPicker1.Value) & " 08:30:00"
sStop = DateAdd("h", 24, CDate(M_S))
sStart = DateAdd("h", -8, CDate(sStart))
sStop = DateAdd("h", -8, CDate(sStop))
sSql = "Tag:R,('1hour\PS4/B123.FQ1500_REAL'),'" & sStart & "','" & sStop & "' order by datetime" '修改要讀取的歸檔變數
oCom.CommandText = sSql
Set oRs = oCom.Execute
'填充資料
M_Row = 6 '修改的地方
M_Col = 3 '修改的地方
TT = 0
If (oRs.EOF) Then '用戶表格資料填寫的起始位 判斷不為空
For M_Hour = 6 To 30
Sheet1.Cells(M_Row + M_Hour - 6, M_Col) = 0
Next M_Hour
Else
oRs.MoveFirst '將指標指向最高位
Sheet1.Cells(M_Row + M_Hour - 8, M_Col) = oRs.Fields(2).Value
End If
End If
oRs.Close
Set oRs = Nothing
uj5u.com熱心網友回復：
VBA中可以單步除錯的，你可以一步一步的 F8看哪一句的執行結果不正確，再來分析原因。
uj5u.com熱心網友回復：
M_S = Year(DTPicker1.Value) & "-" & Month(DTPicker1.Value) & "-" & Day(DTPicker1.Value) & " 08:30:00"寫成這樣的就行了：
M_S = Format$(DTPicker1.Value, "yyyy-m-d") & " 08:30:00"
uj5u.com熱心網友回復：
你可以試試用Wincc的腳本功能，把需要的數值存入Excel中，不知道能不能解決問題。轉載請註明出處，本文鏈接：https://www.uj5u.com/gongcheng/75289.html