Author Topic: Copy 500 element array to Excel?  (Read 5262 times)

bob1371

  • Newbie
  • *
  • Posts: 35
    • View Profile
Copy 500 element array to Excel?
« on: September 12, 2017, 08:00:12 PM »
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

  • Administrator
  • Hero Member
  • *****
  • Posts: 5261
    • View Profile
    • AdvancedHMI
Re: Copy 500 element array to Excel?
« Reply #1 on: September 12, 2017, 08:23:17 PM »
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

  • Newbie
  • *
  • Posts: 35
    • View Profile
Re: Copy 500 element array to Excel?
« Reply #2 on: September 12, 2017, 10:29:18 PM »
Archie,

I would use a bit from PLC to do this.

Thanks.

Archie

  • Administrator
  • Hero Member
  • *****
  • Posts: 5261
    • View Profile
    • AdvancedHMI
Re: Copy 500 element array to Excel?
« Reply #3 on: September 12, 2017, 10:47:41 PM »
- 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

  • Newbie
  • *
  • Posts: 35
    • View Profile
Re: Copy 500 element array to Excel?
« Reply #4 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

Archie

  • Administrator
  • Hero Member
  • *****
  • Posts: 5261
    • View Profile
    • AdvancedHMI
Re: Copy 500 element array to Excel?
« Reply #5 on: September 13, 2017, 07:27:35 AM »
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

  • Newbie
  • *
  • Posts: 35
    • View Profile
Re: Copy 500 element array to Excel?
« Reply #6 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

Archie

  • Administrator
  • Hero Member
  • *****
  • Posts: 5261
    • View Profile
    • AdvancedHMI
Re: Copy 500 element array to Excel?
« Reply #7 on: September 13, 2017, 08:16:41 PM »
Can you post your line 37 of your code, the place where the error occurs.

bob1371

  • Newbie
  • *
  • Posts: 35
    • View Profile
Re: Copy 500 element array to Excel?
« Reply #8 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.
« Last Edit: September 13, 2017, 09:08:18 PM by bob1371 »

bob1371

  • Newbie
  • *
  • Posts: 35
    • View Profile
Re: Copy 500 element array to Excel?
« Reply #9 on: September 13, 2017, 09:11:05 PM »
Image attached

Archie

  • Administrator
  • Hero Member
  • *****
  • Posts: 5261
    • View Profile
    • AdvancedHMI
Re: Copy 500 element array to Excel?
« Reply #10 on: September 13, 2017, 09:15:04 PM »
The tag name needs to be in quotes:

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

bob1371

  • Newbie
  • *
  • Posts: 35
    • View Profile
Re: Copy 500 element array to Excel?
« Reply #11 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.

bob1371

  • Newbie
  • *
  • Posts: 35
    • View Profile
Re: Copy 500 element array to Excel?
« Reply #12 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.

bob1371

  • Newbie
  • *
  • Posts: 35
    • View Profile
Re: Copy 500 element array to Excel?
« Reply #13 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


Archie

  • Administrator
  • Hero Member
  • *****
  • Posts: 5261
    • View Profile
    • AdvancedHMI
Re: Copy 500 element array to Excel?
« Reply #14 on: September 17, 2017, 04:48:16 AM »
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