Skip to Content

Register Login

Industry Online Support

Technical Forum

Navigation

WinCC Reports in Excel

Created by: Reva P at: 7/28/2009 1:41 PM (7 Replies)

Rating (4)

Thanks 4

8 Entries

7/28/2009 1:41 PM	Rate (1)
Reva P Bronze Member Joined: 7/28/2009 Last visit: 5/10/2022 Posts: 90 Rating: (7)	Hi, I am able to generate an excel sheet carrying my data using a vb script as attached. I am able to get it on a button click. The thing is I need to generate an excel sheet in a particular path with a different name, probably along with date and time every time I click the button. What is the script that should be added along with the original script in order to get what I need? Please help. Also, the excel sheet that gets generated should be protected from being editable. For this, I have tried using the protect/uprotect option in review tab of excel. It helps but it does not update current values and shows only the previous updation (before defining protect option in excel). Attachment excel.JPG (2313 Downloads)
	Cheers Revathy
Suggestion To thank Quote Answer
This contribution was helpful to 1 thankful Users santhosh sundaram

7/28/2009 3:20 PM	Rate (0)
Manuelzes Posts: 99 Rating: (7)	Mayby you can create a internal 8 bit char tag. for date dim oTag 'character tag for date set oTag = HMIRuntime.Tags("TAG") oTag.Write(CStr(Date)) 'makes a string of the date and write it to tag so tag is date in your code Srtring becomes "D:\ORCHID\BACKUP\REPORTS\AIR CONSUMPTION"& oTag.Read &".XLS" then you have an excelfile with date. for folder the same way for creating a folder see this site http://www.pcreview.co.uk/forums/thread-1469322.php greetings ME
Manuelzes Posts: 99 Rating: (7)
Suggestion To thank Quote Answer
This contribution was helpful to 1 thankful Users Mr PCS7_V_6_X

7/29/2009 10:56 AM	Rate (0)
ddg Posts: 261 Rating: (42)	Hi revathy, This is the code (VBS) by which you can create an excel file by clicking a button. Sub OnClick(ByVal Item) Dim Excel Dim XLS_File Dim SheetName XLS_File = "D:\Test_excel.xls" SheetName = "New Sheet Name" Set Excel = CreateObject("Excel.Application") Excel.Workbooks.Add Excel.Worksheets(1).Name = SheetName Excel.Worksheets(SheetName).range("A1") = "test by ddg" Excel.Worksheets(1).SaveAs (XLS_File) Excel.Quit Set Excel = Nothing End Sub If you want to execute the script with a trigger time base then assign it in Win CC Global script VBS editor and give required trigger (As per your required time). Regards ddg
ddg Posts: 261 Rating: (42)
Suggestion To thank Quote Answer
This contribution was helpful to 1 thankful Users Josh Elliott

7/30/2009 6:12 AM	Rate (0)
Niranjan Prasad Posts: 691 Rating: (37)	Dear ddg I tried this script but was getting one problem I created one Internal Tag of "Text Tag 8 bit Character Set", and assigned that to 'filename' in the variable in the VB Script. But it was not taking the path which I have given as ("F:\Try.xls") or (F:\try.xls) And when I am assigning the same path inside the script without using the Tag, Its creating the Excel File Where I have given the Path My Intention is to take the Path and Filename from the Screen create it in the Path specified to it I Attaching the Screenshots of the tag, the Screen and the Script Attachment WinCC Script.zip (1338 Downloads)
Niranjan Prasad Posts: 691 Rating: (37)
Suggestion To thank Quote Answer

7/30/2009 8:24 AM	Rate (2)
ddg Posts: 261 Rating: (42)	Ninja Robot My Intention is to take the Path and Filename from the Screen create it in the Path specified to it hi Ninja Robot, Try the script. Dim Excel Dim XLS_File Dim SheetName Dim V(2) V(1) = HMIRuntime.Tags("File_name").Read V(2) = HMIRuntime.Tags("Sheet_name").Read XLS_File = V(1) SheetName = V(2) Set Excel = CreateObject("Excel.Application") Excel.Workbooks.Add Excel.Worksheets(1).Name = SheetName Excel.Worksheets(SheetName).range("A1") = "test by ddg" Excel.Worksheets(1).SaveAs (XLS_File) Excel.Quit Set Excel = Nothing //............ Also see the ref document. regards ddg Attachment ref.zip (1035 Downloads)
ddg Posts: 261 Rating: (42)
Suggestion To thank Quote Answer
This contribution was helpful to 1 thankful Users Niranjan Prasad

7/30/2009 9:14 AM	Rate (0)
Niranjan Prasad Posts: 691 Rating: (37)	Thanks For Replying I got my my Fault in my Script. I was not using ".Read" in the Tag Function for reading the value from the Tag
Niranjan Prasad Posts: 691 Rating: (37)
Suggestion To thank Quote Answer

8/12/2009 1:39 PM	Rate (1)
Reva P Bronze Member Joined: 7/28/2009 Last visit: 5/10/2022 Posts: 90 Rating: (7)	thanx... it works
	Cheers Revathy
Suggestion To thank Quote Answer

8/24/2009 10:53 AM	Rate (0)
Reva P Bronze Member Joined: 7/28/2009 Last visit: 5/10/2022 Posts: 90 Rating: (7)	Also, the excel sheet that gets generated should be protected from being editable. For this, I have tried using the protect/uprotect option in review tab of excel. It helps but it does not update current values given from the code. If I remove the option, the values are updated but it is editable. Please help as to what can be done for this. Regards Revathy
	Cheers Revathy
Suggestion To thank Quote Answer

|

Share this page:

Share this page on...

Follow us on