plc.myforum.ro PLCs users www.plc.myforum.ro!    FAQ   Search   Memberlist   Usergroups   Register   Profile   Log in to check your private messages   Log in  Export from WinCC arhive into Excel file          plc.myforum.ro Forum Index -> SIEMENS SIMATIC HMI View previous topic :: View next topic   Author Message Nicu Junior Joined: 29 Mar 2013 Posts: 4 Posted: 11/04/2013 10:35    Post subject: Export from WinCC arhive into Excel file How can i export from arhive WinCC into Excel file with a C or Vb script ? I must to do daily,monthly reports. I can export data with this vb script but i cant show data&time for every read data. Sub OnClick(ByVal Item) 'VBS113 Dim objExcelApp Set objExcelApp = CreateObject("Excel.Application") objExcelApp.Visible = True objExcelApp.Workbooks.Open "D:\Download\ExcelExample.xls" objExcelApp.Cells(1, 2).Value =ScreenItems("Water1").ObjectName objExcelApp.Cells(2, 2).Value =ScreenItems("I/O Field1").OutputValue objExcelApp.Cells(1, 3).Value =ScreenItems("Water2").ObjectName objExcelApp.Cells(2, 3).Value =ScreenItems("I/O Field2").OutputValue objExcelApp.Cells(1, 4).Value =ScreenItems("Water3").ObjectName objExcelApp.Cells(2, 4).Value =ScreenItems("I/O Field3").OutputValue objExcelApp.ActiveWorkbook.Save 'objExcelApp.Workbooks.Close 'objExcelApp.Quit Set objExcelApp = Nothing End Sub Daily report must be like attachment. [url=http://www.99poze.ro/p/23096a634e7867e59c88f0a47a90ec5d.jpg][/url]+ Thank you Back to top messer Senior Joined: 08 Mar 2012 Posts: 2059 Location: Europe Posted: 11/04/2013 21:58    Post subject: 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. Code: Dim fso, InFile, OutFile, i, hold, hold_format Set fso = CreateObject("Scripting.FileSystemObject") Set InFile = fso.OpenTextFile("c:\Logs\Change_DataLog0.csv",1, True) Set OutFile = fso.CreateTextFile("C:\changes.html", True) OutFile.WriteLine("<html><table>") 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 Back to top messer Senior Joined: 08 Mar 2012 Posts: 2059 Location: Europe Posted: 11/04/2013 21:59    Post subject: This pops up a save file dialog and saves a csv file to an excel file. I hope this helps getting you started. Code: 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 = fso.OpenTextFile("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 Back to top Nicu Junior Joined: 29 Mar 2013 Posts: 4 Posted: 12/04/2013 10:06    Post subject: nicu messer wrote: This pops up a save file dialog and saves a csv file to an excel file. I hope this helps getting you started. Code: 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 = fso.OpenTextFile("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 Good morning I dont have good knowleage of C or vb scripts. I try to understand codes you show me but i fail. Maybe you have a project exemple where i can change my tags and i print daily report. I try arhived tags at 1 hour but this dont work when WinCC proiect stoped.Then project must start at fix hour. Sorry for my english. Thank you Back to top Nicu Junior Joined: 29 Mar 2013 Posts: 4 Posted: 16/04/2013 07:12    Post subject: nicu Thank you Back to top Display posts from previous:  Oldest FirstNewest First         plc.myforum.ro Forum Index -> SIEMENS SIMATIC HMI All times are GMT + 2 Hours Page 1 of 1   Jump to: Select a forum PLC's Users International Forum----------------Board RulesPLC TutorialsSIMATIC Automation Systems -Reg.USimatic Automation SystemsLinksGE Fanuc Emerson Reg. UGE Fanuc Emerson ALLEN-BRADLEY(Rockwell Automation) Reg. UALLEN-BRADLEY(Rockwell Automation)OMRON Reg. UOMRONHONEYWELLSchneider_Electric Reg. USchneider ElectricOther PLCs Reg. UOther PLCsSIEMENS SIMATIC HMI- Reg. USIEMENS SIMATIC HMIOther HMI-Reg. UOther HMICNC & Robotics-Reg. UCNC & RoboticsProgramming Reg. UProgrammingAutoCAD Reg. UAutoCADRenewable EnergyBuilding Technology ManagementPLC BooksCables, Sensors,Transducers ...Drives & InvertersSiemens Hardware & SoftwareSafety Military & AerospaceUtilitiesFAQs & NewsJobsDisclaimer Romanian Users Forum----------------ROSIEMENS SIMATIC Automation SystemsROSIEMENS SIMATIC HMIROSimatic ROther PLCsROther HMIBooksROCADRUtilitieseLearningSimulatorsApplication Examples  You cannot post new topics in this forum You cannot reply to topics in this forum You cannot edit your posts in this forum You cannot delete your posts in this forum You cannot vote in polls in this forum Powered by phpBB © 2001, 2005 phpBB Group Fa-ti si tu propriul forum GRATUIT, pe www.myforum.ro