Sub get_wincc_data()
‘–Get Database DSN name———————————-
Set DSNName = CreateObject("CCHMIRuntime.HMIRuntime")
sDsn = DSNName.Tags("@DatasourceNameRT").Read
‘–build connection string——————————–
sPro = "Provider = WinCCOLEDBProvider.1;"
sDsn = "Catalog=" & sDsn & ";"
sSer = "Data Source=J1YOORGD4LDJVTJ.\WinCC"
sCon = sPro & sDsn & sSer
Set conn = CreateObiect("ADODB.Command")
conn.ConnectionString = sCon
conn.CursorLocation = 3
conn.Open
Set oRs = CreateObject("ADOB.Recordset")
Set oCom = CreateObject("ADOB.Command")
oCom.CommandType = 1
Set oCom.ActiveConnection = conn
‘查询起止时间
sStart = Year(DTPicker1.Value) & "-" & Month(DTPicker1.Value) & "-" & Day(DTPicker1.Value) & "00:00:00"
sStop = Year(DTPicker1.Value) & "-" & Month(DTPicker1.Value) & "-" & Day(DTPicker1.Value) & "23:00:00"
‘转为UTC时间
sStart = DataAdd("h", -8, CDate(sStart))
sStop = DataAdd("h", -8, CDate(sStop))
‘读取1#泵电流
sSql = "Tag:R,(‘ProcessValueArchive\1#分站1#泵_A相电流’),’" & sStart & "’,’" & sStop & "’order by datetime"
oCom.CommandText = sSql
Set oRs = oCom.Execute
If (oRs.EOF) Then
oRs.Close
Else
oRs.MoveFirst
i = 0
Do While Not oRs.EOF
Sheet1.Cells(i + 4, 2) = oRs.Fields("RealValue").Value
i = i + 1
Loop
oRs.Close
End If
Private Sub DTPicker1_Chanfe()
get_wincc_data
End Sub
问题补充：
单改的第十行可以啦但是Dateadd那有报子程序和函数未定义
最佳答案
spro = "Provider=WinCCOLEDBProvider.1;"
sdsn = "Catalog=CC_upzjg_11_12_15_14_45_14R;"
sser = "Data Source=.\WinCC"
scon = spro + sdsn + sser
ssql = "Tag:R,(”test\testdata”),”2011-12-15 7:27:00”,”2011-12-15 8:27:00”"
MsgBox "open with:" &vbCr & scon &vbCr &ssql &vbCr
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
ocom.CommandText = ssql
Set ors=ocm.Execute
/////////////////////////
你的第十行的指令“Set conn = CreateObiect("ADODB.Command")”应该是 “Set conn = CreateObject("ADODB.Connection")”
提问者对于答案的评价：
谢谢，脚本改好啦！
最佳答案作者回复:
sStart = DataAdd("h", -8, CDate(sStart))
sStop = DataAdd("h", -8, CDate(sStop))
这两个的data应该是date
http://www.52plc.net/read.php?tid=4268
评论字数太少了，你看看这个，是我做的完整的脚本。
原创文章，作者：more0621，如若转载，请注明出处：https://www.zhaoplc.com/plc221669.html
微信扫一扫 
支付宝扫一扫 