wincc to excel file

CharlesM · Dec 19, 2005

This reads a csv file from a log and puts it into an HTML table. For excel change the .html to .xls and it will read it fine.

Dim fso, InFile, OutFile, i, hold, hold_format
Set fso = CreateObject("Scripting.FileSystemObject")
Set InFile = fs

penTextFile("c:\Logs\Change_DataLog0.csv",1, True)
Set OutFile = fso.CreateTextFile("C:\changes.html", True)
OutFile.WriteLine("<html><table border =1>")
Do While InFile.AtEndOfStream <> True
hold = InFile.ReadLine
hold_format = "<tr><td>"
For i = 1 To Len(hold)-1
hold = Replace(hold,Chr(34),"")
If StrComp(Mid(hold,i,1),",",1) <> 0 Then
hold_format = hold_format & Mid(hold,i,1)
Else
hold_format = hold_format & "</td><td>"
End If
Next
OutFile.WriteLine(hold_format)
hold_format = "</td></tr>"
Loop

OutFile.WriteLine("</table></html>")
InFile.Close
OutFile.Close

CharlesM · Dec 19, 2005

This pops up a save file dialog and saves a csv file to an excel file. I hope this helps getting you started. WinCC Flex does not support all VB script command but sometimes you can find a work aroung.

Dim filename , myvar

Dim fso, MyFile, i, file_length, hold, TempFile, file_name
Dim InFile, OutFile, hold_format, run_code, cycle_time
Const ForReading = 1, ForWriting = 2, ForAppending = 8
Dim start_time, stop_time, run_min

Set myvar = CreateObject("SAFRCFileDlg.FileSave")
myvar.FileType = "xls"
myvar.FileName = "Order_Report_" & Month(Date) & "_" & Day(Date) & "_" & Year(Date)
myvar.OpenFileSaveDlg
file_name = myvar.FileName & ".xls"
If file_name <> "" Then
' read csv file and write xls file

Set fso = CreateObject("Scripting.FileSystemObject")
Set InFile = fs

penTextFile("c:\job_report.csv",ForReading, True)
Set OutFile = fso.CreateTextFile(file_name, True)
'add title
OutFile.WriteLine("<html>Order Report "& Date &" "& Time)
'add header
OutFile.WriteLine("<table><tr><td>Date </td><td> Time</td><td>Part Number</td><td>Quantity</td><td>Quantity to Date</td><td>Pad #</td><td>Operator</td><td></thead>")
'add data

Do While InFile.AtEndOfStream <> True
hold = InFile.ReadLine
hold = InFile.ReadLine
hold_format = "<tr><td>"
For i = 1 To Len(hold)
hold = Replace(hold,Chr(34),"")
If StrComp(Mid(hold,i,1),",",1) <> 0 Then
hold_format = hold_format & Mid(hold,i,1)
Else
hold_format = hold_format & "</td><td>"
End If
Next
OutFile.WriteLine(hold_format)
hold_format = "</td></tr>"
Loop

OutFile.WriteLine("</table></html>")
InFile.Close
OutFile.Close
End If

catalin78 · Dec 19, 2005

thanks charlesm , how should i proceed in the easyest way to put in the same file , the process data ... and some info about alarms ?
Thanks for the code

CharlesM · Dec 19, 2005

I would start by setting up an alarm log. The WinCC Flex help file should show you how to set this up to log to a csv file. Then create the script and paste in what I have changing the file path to match your log file path. Then call the script on a button click to start just to make sure it works. Then build on what you have working.

empire_nightmare · Dec 19, 2005

hi all .

my name : Vu Duc Duy , student , Viet Nam . I am not good in english .
you can give me some exemples : WinCC to Excel ??
my email : [email protected]
or : you can upload on : www.yousendit.com and show me its link :
thanhks a lot

catalin78 · Dec 20, 2005

log values

Thanks CharlesM your a real saver , the second script works like it appends data at the end of data already in the xls file or overwritting?

Merry Christmas :>

seppoalanen · Dec 21, 2005

catalin78 said:
Hello .
I am trying to get out some data from wincc flexible advanced to a excel file whitout succeeding .
If anybody has a ideea ... please asist ,thanks

I did it yesterday for MP270B 6" touch pabel. They don't tell that there have to be optional Compact Flas Card in OP. It's path is "Storage Card2" not the 1'st default "Storage Card".
I did Alarm history-log and one log for what operator have done.
In that panel have many ways get the .csv-file fe. ethernet or directly from the Compact Flash Card by card reader. I didn't need any script for that.

catalin78 · Dec 22, 2005

seppoalanen , i need to export alarm and process value in a excel file because these are the keys of the problem , and i use a pc runtime not op

wincc to excel file

CharlesM

Member

CharlesM

Member

catalin78

Member

CharlesM

Member

empire_nightmare

Member

catalin78

Member

seppoalanen

Member

catalin78

Member

Similar Topics