A0579_在WinCC中如何使用VBS读取变量归档数据到EXCEL
2个回答
展开全部
读取变量归档的查询语句是:
TAG:R, <ValueID or ValueName>,<TimeBegin>,<TimeEnd>[,<SQL_clause>][,<TimeStep>]
其中TimeStep指的是时间间隔
Sub OnLButtonDown(ByVal Item, ByVal Flags, ByVal x, ByVal y)
Dim sPro
Dim sDsn
Dim sSer
Dim sCon
Dim sSql
Dim conn
Dim oRs
Dim oCom
Dim oItem
'Dim m,n,s
'connection string
sPro = "Provider=WinCCOLEDBProvider.1;"
sDsn = "Catalog=CC_XXXXX_12_03_10_08_18_49R;" '数据库名
sSer = "Data Source=.\WinCC"
sCon = sPro + sDsn + sSer
'sCon="Provider=WinCCOLEDBProvider.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=SSEJRecord;Data Source=.\WINCC"
'MsgBox "set sCon"
'sSQL为查询命令
sSql = "TAG:R,'ProcessValueArchive\AnalogTag','0000-00-00 00:00:20.000','0000-00-00 00:00:00.000'"
'sSql = "ALARMVIEW:SELECT * from AlgViewChs"
'MsgBox "Set SQL"
'建立连接
Set conn = CreateObject("ADODB.Connection")
conn.ConnectionString = sCon
conn.CursorLocation = 3
conn.Open
'MsgBox "conn Open"
'进行查询
Set oRs = CreateObject("ADODB.Recordset")
Set oCom = CreateObject("ADODB.Command")
oCom.CommandType = 1
Set oCom.ActiveConnection = conn
oCom.CommandText = sSql
'MsgBox "set command"
'填充记录集
Set oRs = oCom.Execute
'm = oRs.Fields.Count
MsgBox oRs.EOF
If(oRs.EOF) Then
oRs.Close
Else
oRs.MoveFirst
Do While Not oRs.EOF
HMIRuntime.Tags("AlarmText1").Write CStr(oRs.Fields(17).Value)
oRs.MoveNext
Loop
oRs.Close
End If
Set oRs = Nothing
conn.Close
Set conn = Nothing
'MsgBox "end"
End Sub
下面是WINCC读写EXCEL的脚本
写:
Sub OnOpen()
Dim objComboBox
Set objComboBox = ScreenItems("控件1")
objComboBox.Clear()
Dim objExcelApp,oWorkBook
Set objExcelApp = CreateObject("Excel.Application")
objExcelApp.Visible = False
Set oWorkBook = objExcelApp.Workbooks.Open("c:\1.xls")
objComboBox.AddItem objExcelApp.Cells(1, 1).Value
objComboBox.AddItem objExcelApp.Cells(1, 2).Value
objComboBox.AddItem objExcelApp.Cells(1, 3).Value
objComboBox.SelText = "选项1"
objExcelApp.Workbooks.Close
objExcelApp.Quit
Set objExcelApp = nothing
End Sub
读:
Sub OnLButtonDown(Byval Item, Byval Flags, Byval x, Byval y)
Dim objExcelApp,oWorkBook
Set objExcelApp = CreateObject("Excel.Application")
objExcelApp.Visible = False
Set oWorkBook = objExcelApp.Workbooks.Open("c:\1.xls")
Dim iBlankLine
iBlankLine = oWorkBook.ActiveSheet.Columns(1).Find("").Row '查找第一列的空行
'MsgBox iBlankLine
objExcelApp.Cells(iBlankLine, 1).Value = "tag1"
objExcelApp.Cells(iBlankLine, 2).Value = "tag2"
objExcelApp.Cells(iBlankLine, 3).Value = "tag3"
oWorkBook.Save
objExcelApp.Workbooks.Close
objExcelApp.Quit
Set objExcelApp = nothing
'Set oWorkBook =objExcelApp.Workbooks.Add
'oWorkBook.SaveAs "D:\ExcelExample.xls"
End Sub
以上都是我写的 现在还在用 这两部分合起来就能完成你的功能了
TAG:R, <ValueID or ValueName>,<TimeBegin>,<TimeEnd>[,<SQL_clause>][,<TimeStep>]
其中TimeStep指的是时间间隔
Sub OnLButtonDown(ByVal Item, ByVal Flags, ByVal x, ByVal y)
Dim sPro
Dim sDsn
Dim sSer
Dim sCon
Dim sSql
Dim conn
Dim oRs
Dim oCom
Dim oItem
'Dim m,n,s
'connection string
sPro = "Provider=WinCCOLEDBProvider.1;"
sDsn = "Catalog=CC_XXXXX_12_03_10_08_18_49R;" '数据库名
sSer = "Data Source=.\WinCC"
sCon = sPro + sDsn + sSer
'sCon="Provider=WinCCOLEDBProvider.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=SSEJRecord;Data Source=.\WINCC"
'MsgBox "set sCon"
'sSQL为查询命令
sSql = "TAG:R,'ProcessValueArchive\AnalogTag','0000-00-00 00:00:20.000','0000-00-00 00:00:00.000'"
'sSql = "ALARMVIEW:SELECT * from AlgViewChs"
'MsgBox "Set SQL"
'建立连接
Set conn = CreateObject("ADODB.Connection")
conn.ConnectionString = sCon
conn.CursorLocation = 3
conn.Open
'MsgBox "conn Open"
'进行查询
Set oRs = CreateObject("ADODB.Recordset")
Set oCom = CreateObject("ADODB.Command")
oCom.CommandType = 1
Set oCom.ActiveConnection = conn
oCom.CommandText = sSql
'MsgBox "set command"
'填充记录集
Set oRs = oCom.Execute
'm = oRs.Fields.Count
MsgBox oRs.EOF
If(oRs.EOF) Then
oRs.Close
Else
oRs.MoveFirst
Do While Not oRs.EOF
HMIRuntime.Tags("AlarmText1").Write CStr(oRs.Fields(17).Value)
oRs.MoveNext
Loop
oRs.Close
End If
Set oRs = Nothing
conn.Close
Set conn = Nothing
'MsgBox "end"
End Sub
下面是WINCC读写EXCEL的脚本
写:
Sub OnOpen()
Dim objComboBox
Set objComboBox = ScreenItems("控件1")
objComboBox.Clear()
Dim objExcelApp,oWorkBook
Set objExcelApp = CreateObject("Excel.Application")
objExcelApp.Visible = False
Set oWorkBook = objExcelApp.Workbooks.Open("c:\1.xls")
objComboBox.AddItem objExcelApp.Cells(1, 1).Value
objComboBox.AddItem objExcelApp.Cells(1, 2).Value
objComboBox.AddItem objExcelApp.Cells(1, 3).Value
objComboBox.SelText = "选项1"
objExcelApp.Workbooks.Close
objExcelApp.Quit
Set objExcelApp = nothing
End Sub
读:
Sub OnLButtonDown(Byval Item, Byval Flags, Byval x, Byval y)
Dim objExcelApp,oWorkBook
Set objExcelApp = CreateObject("Excel.Application")
objExcelApp.Visible = False
Set oWorkBook = objExcelApp.Workbooks.Open("c:\1.xls")
Dim iBlankLine
iBlankLine = oWorkBook.ActiveSheet.Columns(1).Find("").Row '查找第一列的空行
'MsgBox iBlankLine
objExcelApp.Cells(iBlankLine, 1).Value = "tag1"
objExcelApp.Cells(iBlankLine, 2).Value = "tag2"
objExcelApp.Cells(iBlankLine, 3).Value = "tag3"
oWorkBook.Save
objExcelApp.Workbooks.Close
objExcelApp.Quit
Set objExcelApp = nothing
'Set oWorkBook =objExcelApp.Workbooks.Add
'oWorkBook.SaveAs "D:\ExcelExample.xls"
End Sub
以上都是我写的 现在还在用 这两部分合起来就能完成你的功能了
推荐律师服务:
若未解决您的问题,请您详细描述您的问题,通过百度律临进行免费专业咨询