Copy 500 element array to Excel?

[bob1371]

Hello,

I've been reading through the forum and particularly looking at the KeepingRecordsFromCLXtoExcelFile demo. I'm looking to copy an array of the last 500 cycle time averages for each of the 7 sections of the cell. A total of 3500 tags to 1 excel file.

I have 0 coding experience but do have a few AHMI projects up and working. I've downloaded the KeepingRecordsFromCLXtoExcelFile AHMI project and have set BasicLabel1 to Array address. Currently when I start the project I get the value from AverageCycleArray[0] displayed. I want to work with getting this to excel then move on to the complete array.

I believe I read in one of the post that I can save this to an excel file even without MS Office installed?
If so how to do this?
Will I need a BasicLabel for each array element?

Thanks,

[Archie]

What do you want to trigger the transfer? A bit in the PLC, a click of a button, etc.?

Once that is determined, you would use a few lines of code to read the PLC data, then write it to the PLC.

[bob1371]

Archie,

I would use a bit from PLC to do this.

Thanks.

[Archie]

- Add a Datasubscriber to your form
- Set PLCAddressValue to the trigger bit
- Double click the DataSubscriber to get back to the code
- Start with this code:
if e.ErrorCode=0 AndAlso e.Values.Count>0 and Also e.Value(0)="True" then
   Dim MyValues() as string=EthernetIPforCLXCom1.Read(AverageCycleArray[0] , 500)
   '* Transfer the values to Excel
End If


In order to write to an Excel file, you will need to use the EPPlus library.
- Project->Manage Nuget Packages
- Select Browse
- In the Seach Bar enter EPPLUS
- Select the top item, then click the Install button

From here I will need to look at the Excel sample project because I do not know the code right off the top of my head.

[bob1371]

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


[Archie]

Start with a blank AdvancedHMI solution then follow the steps above. To continue with my code sample:

Code: [Select]

if e.ErrorCode=0 AndAlso e.Values.Count>0 and Also e.Value(0)="True" then
   Dim MyValues() as string=EthernetIPforCLXCom1.Read(AverageCycleArray[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

You do not want the Microsoft Excel Object Library. That is only used if you have Excel installed and want to control it from AdvancedHMI.

[bob1371]

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

[Archie]

Can you post your line 37 of your code, the place where the error occurs.

[bob1371]

       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.

[bob1371]

Image attached

[Archie]

The tag name needs to be in quotes:

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

[bob1371]

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.

[bob1371]

Archie,

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

Will get back at it tomorrow afternoon.

[bob1371]

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

[Archie]

It should be e.ErrorID
Count should be Length

 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("VCell1A_Actual_Cycle_Time_Real[0]", 500)
            Console.WriteLine(MyValues.Length & " elements read.")

            '* Transfer the values to Excel
            Using ExcelPackage As New OfficeOpenXml.ExcelPackage(New System.IO.FileInfo(".\HistoricalData.xlsx"))
                For I = 0 To MyValues.Length - 1
                    Console.WriteLine("Element " & I & "=" & MyValues(I))
                    ExcelPackage.Workbook.Worksheets(1).Cells(1, 1).Value = MyValues(I)
                Next
            End Using
        End If