Am trying to log data of some tags of WinCC Flex into Excel file using VB Script. I've searched on Siemens forums and have got an script according to my needs. But there is an error while executing the script in WinCC Flex.
The script is as:
Dim wsh, XLSrunning, TargetBookrunning, objExcelApp, objWorkbook, TheTargetBook, TheTargetBookName
Dim TheCount
Dim objFSO
Const OverwriteExisting = 1
Set wsh = CreateObject("WScript.Shell")
TheTargetBookName = "report.xls"
TheTargetBook = "D:\Out\" & TheTargetBookName
'---------------[Modification#1_Begin]-------------------------------------------
Set objFSO = CreateObject("Scripting.FileSystemObject")
If Not objFSO.FileExists(TheTargetBook) Then
objFSO.CopyFile "D:\Out\Template.xls", TheTargetBook, OverwriteExisting
'HMIRuntime.Trace "The file," & TheTargetBook & ", does not exist." & vbCrLf & "I've just created one for you!"
End If
Set objFSO = Nothing
'---------------[Modification#1_End]--------------------------------------------
TheCount = GetObject("winmgmts:root\CIMV2").ExecQuery("SELECT * FROM Win32_Process WHERE Name='EXCEL.EXE'").Count
'While TheCount is bigger than 0, it means the Excel Application is running..., but doesn't mean the workbook is open for sure!
If TheCount > 0 Then
Set objExcelApp = GetObject(,"Excel.Application")
' Using GetObject(,"Excel.Application") to point to the running Excel Application.
TargetBookrunning = 0
For Each XLSrunning In objExcelApp.Workbooks
If XLSrunning.name = TheTargetBookName Then
TargetBookrunning = 1
End If
Next
If TargetBookrunning = 1 Then
Set objWorkbook = GetObject(TheTargetBook)
Else
Set objWorkbook = objExcelApp.Workbooks.Open(TheTargetBook)
End If
Else
Set objExcelApp = CreateObject("Excel.Application")
Set objWorkbook = objExcelApp.Workbooks.Open(TheTargetBook)
End If
objExcelApp.Visible = True
objExcelApp.ScreenUpdating = True
objExcelApp.DisplayAlerts = True
Dim TheTargetRow ' <------[Modification#2]-------
With objWorkbook.ActiveSheet
TheTargetRow = .Cells(65535, 2).End(-4162).Row
.cells(TheTargetRow + 1, 2) = SmartTags("Tag_1")
.cells(TheTargetRow + 1, 3) = SmartTags("Tag_2")
.cells(TheTargetRow + 1, 4) = SmartTags("Tag_3")
End With
objWorkbook.Save
'objWorkbook.Close
Set objWorkbook = Nothing
'objExcelApp.Quit
Set objExcelApp = Nothing
'MsgBox "Done"
Set wsh = Nothing
When, I try to execute this script, the compiler shows the error in following line:
TheTargetRow = .Cells(65535, 2).End(-4162).Row
Am unable to determine the error. Please do the needful.
The code you show is mostly correct, but actually the problem in that line has something to do with the implementation of the VBScript interface inside the WINCC environment.
If you created a new variable called "theSheet" to hold the reference to the Excel worksheet, you would still avoid that syntax checking problem in WinCC.
This way would allow you to access from it to its Cells object, but seems to me that there is no an obvious way to directly return the value of the ".End(xlUp).Row" property, though.
However, the only purpose that the "Row" value looks to have is getting the row number where to print the tag values out. Check the next code and see how you feel with the result.