Messages

16

Open Discussion / Re: Copy 500 element array to Excel?

« on: September 27, 2017, 03:50:48 AM »

I've added 2 more DataSubscribers and now collecting for all 3 cell. I have it set up to label each of the 3 tabs and populate Column A with the data. Everything Good so far.

Code: [Select]

Private Sub DataSubscriber1_DataChanged(sender As Object, e As Drivers.Common.PlcComEventArgs) Handles DataSubscriber1.DataChanged

        If e.ErrorId = 0 AndAlso e.Values.Count > 0 AndAlso e.Values(0) = "True" Then
            Console.WriteLine("About to read the data")
            Dim MyValues() As String = EthernetIPforCLXCom1.Read("VCell_1A_FES_Cycle_Average[0]", 500)
            Console.WriteLine(MyValues.Length & "elements rea8d.")

            '*Transfer the values to Excel
            Using ExcelPackage As New OfficeOpenXml.ExcelPackage(New IO.FileInfo("C:\Data.xlsx"))
                ExcelPackage.Workbook.Worksheets.Add("VCell 1A")
                For Index = 0 To MyValues.Length - 1
                    Console.WriteLine("Element " & Index & "=" & MyValues(Index))
                    Dim ws As OfficeOpenXml.ExcelWorksheet = ExcelPackage.Workbook.Worksheets(1)
                    Console.WriteLine("Worksheet OK")
                    Dim CellNum As String = "A" & (Index + 1)
                    ws.Cells(CellNum).Value = MyValues(Index)
                Next
                ExcelPackage.Save()
            End Using
        End If
    End Sub

    Private Sub DataSubscriber2_DataChanged(sender As Object, e As Drivers.Common.PlcComEventArgs) Handles DataSubscriber2.DataChanged

        If e.ErrorId = 0 AndAlso e.Values.Count > 0 AndAlso e.Values(0) = "True" Then
            Console.WriteLine("About to read the data")
            Dim MyValues() As String = EthernetIPforCLXCom1.Read("Inspect_FES_Cycle_Average[0]", 500)
            Console.WriteLine(MyValues.Length & "elements rea8d.")

            '*Transfer the values to Excel
            Using ExcelPackage As New OfficeOpenXml.ExcelPackage(New IO.FileInfo("C:\Data.xlsx"))
                ExcelPackage.Workbook.Worksheets.Add("Inspect")
                For Index = 0 To MyValues.Length - 1
                    Console.WriteLine("Element " & Index & "=" & MyValues(Index))
                    Dim ws As OfficeOpenXml.ExcelWorksheet = ExcelPackage.Workbook.Worksheets(2)
                    Console.WriteLine("Worksheet OK")
                    Dim CellNum As String = "A" & (Index + 1)
                    ws.Cells(CellNum).Value = MyValues(Index)
                Next
                ExcelPackage.Save()
            End Using
        End If
    End Sub

    Private Sub DataSubscriber3_DataChanged(sender As Object, e As Drivers.Common.PlcComEventArgs) Handles DataSubscriber3.DataChanged

        If e.ErrorId = 0 AndAlso e.Values.Count > 0 AndAlso e.Values(0) = "True" Then
            Console.WriteLine("About to read the data")
            Dim MyValues() As String = EthernetIPforCLXCom1.Read("Repair_FES_Cycle_Average[0]", 500)
            Console.WriteLine(MyValues.Length & "elements rea8d.")

            '*Transfer the values to Excel
            Using ExcelPackage As New OfficeOpenXml.ExcelPackage(New IO.FileInfo("C:\Data.xlsx"))
                ExcelPackage.Workbook.Worksheets.Add("Repair")
                For Index = 0 To MyValues.Length - 1
                    Console.WriteLine("Element " & Index & "=" & MyValues(Index))
                    Dim ws As OfficeOpenXml.ExcelWorksheet = ExcelPackage.Workbook.Worksheets(3)
                    Console.WriteLine("Worksheet OK")
                    Dim CellNum As String = "A" & (Index + 1)
                    ws.Cells(CellNum).Value = MyValues(Index)
                Next
                ExcelPackage.Save()
            End Using
        End If
    End Sub


17

Application Showcase / First project that I just keep adding too.

« on: September 27, 2017, 03:45:47 AM »

I have 11 cells, each with various cells in them. I'm displaying Standard Cycle Time, Actual Cycle Time, Average Cycle Time, and the Deviation from Standard Cycle Time. The Deviation times switch from Green is Ok to Yellow as it approaches 50% and then Red if it goes over standard.

This particular line only has 3 cells so the others are hidden with a visibility bit. I have an alarm banner that will display any of the cells that are in the red. It will cycle through them at 10 second intervals.
 
With lots of help from  this forum I've just implemented a data collection scheme that will collect an array of actual cycle times. Currently doing this by manually toggling a bit but will be adding timers and collecting this at the end of each shift.

I've got this running on an old laptop with HDMI to 50" TV.

18

Open Discussion / Re: Copy 500 element array to Excel?

« on: September 26, 2017, 11:48:40 PM »

+++++++SUCCESS+++++++


Added ExcelPackage.SaveAs () right outside the loop and it's now creating the Excel file and placing data in column A rows 1-500.

Code: [Select]

If e.ErrorId = 0 AndAlso e.Values.Count > 0 AndAlso e.Values(0) = "True" Then
            Console.WriteLine("About to read the data")
            Dim MyValues() As String = EthernetIPforCLXCom1.Read("VCell_1A_FES_Cycle_Average[0]", 500)
            Console.WriteLine(MyValues.Length & "elements read.")

            '* Transfer the values to Excel
            Using ExcelPackage As New OfficeOpenXml.ExcelPackage(New IO.FileInfo("C:\Data.xlsx"))
                ExcelPackage.Workbook.Worksheets.Add("test")
                For Index = 0 To MyValues.Length - 1
                    Console.WriteLine("Element " & Index & "=" & MyValues(Index))
                    Dim ws As OfficeOpenXml.ExcelWorksheet = ExcelPackage.Workbook.Worksheets(1)
                    Console.WriteLine("Worksheet OK")
                    Dim CellNum As String = "A" & (Index + 1)
                    ws.Cells(CellNum).Value = MyValues(Index)
                Next
                ExcelPackage.Save()
            End Using
        End If


I will play around with adding headers to the cells and formatting how I want.

Thanks again....

19

Open Discussion / Re: Copy 500 element array to Excel?

« on: September 26, 2017, 03:15:03 AM »

I think I'm finally making some progress.

I commented out the loop and tried to write just a static value to excel sheet but got the same alarm. I then just let the data output to console and when I looked at the output tab sure enough it's all there 0 - 499.

I put things back then copied the last code Archie posted. I do believe I have it now, with the exception of where is the file located? On the output tab I now have the following.

Code: [Select]

About to read the data
500elements read.
Element 0=32.88
Worksheet OK
Element 1=195.33
Worksheet OK
Element 2=27.885
Worksheet OK
Element 3=51.63
Worksheet OK

All the way down to Element 499.

Code: [Select]

Using ExcelPackage As New OfficeOpenXml.ExcelPackage(New IO.FileInfo("C:\Data.xlsx"))In this line I believe I'm telling it to create file "Data.xlsx in C:\ " but I have no files?

I will keep plugging away. Thanks for all the help so far.

20

Open Discussion / Re: Copy 500 element array to Excel?

« on: September 25, 2017, 04:09:59 AM »

Thanks Archie and Doug.

I will look everything over again when I get into work tonight.

21

Open Discussion / Re: Copy 500 element array to Excel?

« on: September 24, 2017, 06:38:25 PM »

Ok I had not checked the output window previously. I start the application in debug. Toggle my tag for Datasubscriber the close debug.

Here is what I get.
About to read the data
500Elements read.
Element 0=87.945
Exception Thrown: 'System.IndexOutOfRangeException' in EPPlus.dll
The thread 0x22b4 has exited with code 0 (0x0).
The thread 0x214c has exited with code 0 (0x0).
The thread 0x2154 has exited with code 0 (0x0).
The program '[10124] AdvancedHMI.exe' has exited with code 0 (0x0)

So it is actually reading but having trouble writing the Excel file? Do I need to register EPPlus.dll file?

Thanks.

22

Open Discussion / Re: Copy 500 element array to Excel?

« on: September 24, 2017, 04:05:58 AM »

Archie,
Thanks for the additional tutorial resources....


Been a busy week so didn't get to mess with this much.  Only a few lines running tonight so thought I would try again.

As for this issue, I still cannot get data. I have everything as you have shown. I have the EPPLUS installed via Manage NuGet Packages.

I have my datascribber set up with a tag. I can trigger that tag with the application running and still get nothing. Looking at the VB Code during debug I get no errors.

I've started over several times trying a couple different things to no avail

Not sure whats missing.

23

Open Discussion / Re: Copy 500 element array to Excel?

« on: September 17, 2017, 12:11:28 PM »

Great thanks. I will give it a try this afternoon.

I've started an online basic learning VB course. Hopefully be able to figure some of this stuff out soon.

24

Open Discussion / Re: Copy 500 element array to Excel?

« on: September 16, 2017, 10:58:39 PM »

Archie,
Still haven't been able to get this working. I try a few things and it doesn't work. Delete and start over with a fresh AHMI and repeat.
Here are the steps i'm going through.

Fresh instance of AHMI
Open mainform -> Build Solution
Drag EthernetIPforCLXCom1 to form -> Set IP Address
Drag DataScribber to form -> Set PLC tag in PLCAddressValue
Double Click DataScribber -> Paste code that you supplied in post reply #5
Project -> Manage NuGet Packages -> Install EPPlus V4.1.1

I then double click dataScribber again to get to code. I will go line by line and list errors.

Line 33    If e.ErrorCode = 0 AndAlso e.Values.Count > 0 And Also e.Value(0) = "True" Then

1. ErrorCode is not a memeber of PLCComEvent.Args. I can change to ErrorMessage or ErrorID and it's happy.
2. The 2nd And Also has a space. I assume it should be without space as the first one is.
3. If I remove space in And Also then e.Value "Value is not a memeber of PLCComEvent.Args" but I can change to e.Values and it's ok.



Line 39      For I = 0 To MyValues.Count - 1
1. Count is not a member of string ()


I make changes and this is the final code.  I can publish and run the executable. Set my tag to trigger in PLC but still nothing.  I tried added C: to this .\HistoricalData.xlsx (C:\HistoricalData.xlsx) thinking this will be the location and file name but still nothing.



Imports System.Linq

Public Class MainForm
    '*******************************************************************************
    '* Stop polling when the form is not visible in order to reduce communications
    '* Copy this section of code to every new form created
    '*******************************************************************************
    Private NotFirstShow As Boolean

    Private Sub Form_VisibleChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.VisibleChanged
        '* Do not start comms on first show in case it was set to disable in design mode
        If NotFirstShow Then
            AdvancedHMIDrivers.Utilities.StopComsOnHidden(components, Me)
        Else
            NotFirstShow = True
        End If
    End Sub

    '***************************************************************
    '* .NET does not close hidden forms, so do it here
    '* to make sure forms are disposed and drivers close
    '***************************************************************
    Private Sub MainForm_FormClosing(sender As Object, e As FormClosingEventArgs) Handles Me.FormClosing
        Dim index As Integer
        While index < My.Application.OpenForms.Count
            If My.Application.OpenForms(index) IsNot Me Then
                My.Application.OpenForms(index).Close()
            End If
            index += 1
        End While
    End Sub

    Private Sub DataSubscriber1_DataChanged(sender As Object, e As Drivers.Common.PlcComEventArgs) Handles DataSubscriber1.DataChanged

        If e.ErrorMessage = 0 AndAlso e.Values.Count > 0 AndAlso e.Values(0) = "True" Then
            Dim MyValues() As String = EthernetIPforCLXCom1.Read("VCell1A_Actual_Cycle_Time_Real[0]", 500)
            '* Transfer the values to Excel
            Using ExcelPackage As New OfficeOpenXml.ExcelPackage(New System.IO.FileInfo(".\HistoricalData.xlsx"))
                For I = 0 To MyValues.Count - 1
                    ExcelPackage.Workbook.Worksheets(1).Cells(1, 1).Value = MyValues(I)
                Next
            End Using
        End If

    End Sub
End Class

25

Open Discussion / Re: Copy 500 element array to Excel?

« on: September 13, 2017, 11:11:24 PM »

Archie,

Doesn't look like I'm writing to an Excel file.

Will get back at it tomorrow afternoon.

26

Open Discussion / Re: Copy 500 element array to Excel?

« on: September 13, 2017, 09:41:42 PM »

Thanks Archie,

Looks like it may do the trick. I will set up and trigger my DataScriber every 10 minutes and see how it works.

27

Open Discussion / Re: Copy 500 element array to Excel?

« on: September 13, 2017, 09:11:05 PM »

Image attached

28

Open Discussion / Re: Copy 500 element array to Excel?

« on: September 13, 2017, 09:03:50 PM »

       Dim MyValues() As String = EthernetIPforCLXCom1.Read(VCell_1A_FES_Cycle_Average[0], 500)

If I take the [ 0] out (Dim MyValues() As String = EthernetIPforCLXCom1.Read(VCell_1A_FES_Cycle_Average, 500)
errors are gone and it lets me run the script. Not sure if it's working though as I don't know where the file will be stored.

Thanks.

29

Open Discussion / Re: Copy 500 element array to Excel?

« on: September 13, 2017, 08:05:13 PM »

Thanks Archie.

I think I have everything but it doesn't like the

• When calling out the array. Gives the following message

1>C:\Users\Owner\Desktop\AHMI Data Collection\AdvancedHMI\MainForm.vb(37,92): error BC30203: Identifier expected.
1>C:\Users\Owner\Desktop\AHMI Data Collection\AdvancedHMI\MainForm.vb(37,92): error BC32017: Comma, ')', or a valid expression

30

Open Discussion / Re: Copy 500 element array to Excel?

« on: September 13, 2017, 03:20:53 AM »

Archie,

I did get some time to look through this.

I did everything you suggested in last post.
When I tried to get EPPLUS it showed I already had it installed.

I then went on to reading this post.
http://www.advancedhmi.com//forum/index.php?topic=679.0

and tried to add Microsoft Excel Object Library but it could not be found.

That's where I'm stuck at this time.

Here is my Mainform.VB code.

Thanks.

Code: [Select]

Public Class MainForm
    '*******************************************************************************
    '* Stop polling when the form is not visible in order to reduce communications
    '* Copy this section of code to every new form created
    '*******************************************************************************
    Private NotFirstShow As Boolean
    Private ReadOnly VCell_1A_FES_Cycle_Average As String
    Private ReadOnly oSheet As Object

    Private Sub Form_VisibleChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.VisibleChanged
        ''* Do not start comms on first show in case it was set to disable in design mode
        'If NotFirstShow Then
        '    AdvancedHMIDrivers.Utilities.StopComsOnHidden(components, Me)
        'Else
        '    NotFirstShow = True
        'End If
    End Sub

    '***********************************************************
    '* When the BasicLabel gets a new value, this event fires
    '***********************************************************
    Private Sub BasicLabel1_ValueChanged(sender As Object, e As EventArgs) Handles BasicLabel1.ValueChanged
        If BasicLabel1.Value = "True" Then
            Try
                '***************************************
                '* Open the existing Excel file
                '***************************************
                Using ExcelPackage As New OfficeOpenXml.ExcelPackage(New System.IO.FileInfo(".\HistoricalData.xlsx"))
                    '***************************************
                    '* Search for the first blank Excel row
                    '***************************************
                    Dim FirstBlankRow As Integer = 1
                    While ExcelPackage.Workbook.Worksheets(1).Cells(FirstBlankRow, 1).Value IsNot Nothing
                        FirstBlankRow += 1
                    End While

                    '**************************************
                    '* Show om the form the row to use
                    '**************************************
                    StatusLabel.Text = "Status: Blank Row found at " & FirstBlankRow

                    '***********************************************************************
                    '* Read the first tag within the UDT, then store in the Excel Worksheet
                    '***********************************************************************
                    Dim StartTime As String = EthernetIPforCLXCom1.Read("FillRecords[0].StartTime")
                    ExcelPackage.Workbook.Worksheets(1).Cells(FirstBlankRow, 1).Value = StartTime

                    '***********************************************************************
                    '* Read the second tag within the UDT, then store in the Excel Worksheet
                    '***********************************************************************
                    Dim EndTime As String = EthernetIPforCLXCom1.Read("FillRecords[0].EndTime")
                    ExcelPackage.Workbook.Worksheets(1).Cells(FirstBlankRow, 1).Value = EndTime


                    '**********************************
                    '* Save the Excel file changes
                    '**********************************
                    StatusLabel.Text = "Status: Saving Excel File on " & Now
                    ExcelPackage.Save()

                    '***********************************************************************
                    '* Clear the TruckCompleted bit which is monitored with the BasicLabel
                    '***********************************************************************
                    EthernetIPforCLXCom1.Write(BasicLabel1.PLCAddressValue, "0")
                End Using
            Catch ex As Exception
                StatusLabel.Text = "Status: Error = " & ex.Message
            End Try
        End If
    End Sub

    Private Sub Label2_Click(sender As Object, e As EventArgs) Handles Label2.Click

    End Sub

    Private Sub MainForm_Load(sender As Object, e As EventArgs) Handles MyBase.Load

    End Sub

    Private Sub DataSubscriber1_DataChanged(sender As Object, e As Drivers.Common.PlcComEventArgs)
        If e.ErrorMessage = 0 AndAlso e.Values.Count > 0 AndAlso e.Values(0) = "true" Then
            Dim MyValues() As String = EthernetIPforCLXCom1.Read(VCell_1A_FES_Cycle_Average(0), 500)
            oSheet.Cells(1, 1).Value = e.Values(0)
            '* Transfer the values to Excel
        End If
    End Sub

    Private Sub EthernetIPforCLXCom1_DataReceived(sender As Object, e As Drivers.Common.PlcComEventArgs) Handles EthernetIPforCLXCom1.DataReceived

    End Sub

    Private Sub BasicLabel1_Click(sender As Object, e As EventArgs) Handles BasicLabel1.Click

    End Sub

    Private Sub DataSubscriber1_DataChanged_1(sender As Object, e As Drivers.Common.PlcComEventArgs) Handles DataSubscriber1.DataChanged

    End Sub

    Private Sub QuickStartLabel_Click(sender As Object, e As EventArgs) Handles QuickStartLabel.Click

    End Sub
End Class