Messages

1

Open Discussion / Re: Control Logix array to Excel in 2024

« on: November 12, 2024, 11:16:45 AM »

Bachphi, Nothing but gratitude from this corner and I'm so sorry I misspelled your name!  You're right.  The tutoring you and Archie did was of the "Here is exactly how to do it" sort and much appreciated.  I certainly didn't mean to offend anyone, my first time in the forum here.  The help you and Archie gave was right on target and very prompt.  That is something generously given and very much appreciated.

2

Open Discussion / Re: Control Logix array to Excel in 2024

« on: November 11, 2024, 09:06:04 PM »

Thanks SO much to Archie and Bachpie for tutoring me a little.  I now have an AdvancedHMI with a single button.  All my Visual Basic code is in the button routine.  When you push the button it reads two arrays of 400 integers from the RsLogix PLC and writes them into an existing spreadsheet HistogramData.xlsx (overwriting the previous data).  I have a second spreadsheet that links to HistogramData and charts and analyses it.

My code looks like this:

Private Sub BasicButton1_Click(sender As Object, e As EventArgs) Handles BasicButton1.Click
    Dim Hist500Data() As String = EthernetIPforCLXCom1.Read("Histogram500[0]", 400)
    Dim Hist1000Data() As String = EthernetIPforCLXCom1.Read("Histogram1000[0]", 400)
    Dim StrNumber As String
    Dim filepath As String = ".\HistogramData.xlsx"

    Using WB As New ClosedXML.Excel.XLWorkbook(filepath)

        For i As Integer = 0 To Hist500Data.GetLength(0) - 1

            If IsNumeric(Hist500Data(i)) Then
                WB.Worksheet(1).Cell(i + 2, 2).Value = CInt(Hist500Data(i))
            Else WB.Worksheet(1).Cell(i + 2, 2).Value = 0
            End If
            If IsNumeric(Hist1000Data(i)) Then
                WB.Worksheet(1).Cell(i + 2, 3).Value = CInt(Hist1000Data(i))
            Else WB.Worksheet(1).Cell(i + 2, 3).Value = 0
            End If
        Next

        WB.Save()
    End Using
End Sub

3

Open Discussion / Re: Control Logix array to Excel in 2024

« on: November 08, 2024, 08:45:36 PM »

Ok guys, yes, Worksheet(1) instead of Worksheet(0) fixes it and it works as Archie describes.  Adding in  the code to write an array to the worksheet, I can get it to write a one-dimensional array into my worksheet with the code shown below.  I'm almost all the way there!!  The final step for me is to have my data source be a ControlLogix tag.  In the quick start Youtube tutorial I made the link to a PLC tag name by setting a property of the panel meter I had placed on my main panel.  Here, I have no visual control I'm working with so how do I reference the 400 element float array with tag name "Histogram1" that exists within my ControlLogix program in my Visual Basic code?
----------------------------------------------------------------------------------------------------------------------
Private Sub MainForm_Load(sender As Object, e As EventArgs) Handles MyBase.Load
    Dim WB As New ClosedXML.Excel.XLWorkbook()
    Dim FloatData As Double() = {1.1, 2.2, 3.3, 4.4, 5.5, 6.6, 7.7, 8.8, 9.9}

    WB.AddWorksheet()

    For i As Integer = 0 To FloatData.GetLength(0) - 1
        WB.Worksheet(1).Cell(i + 2, 2).Value = FloatData(i)
    Next
    WB.SaveAs(".\HistogramExample.xlsx")
End Sub

4

Open Discussion / Re: Control Logix array to Excel in 2024

« on: November 08, 2024, 06:17:05 PM »

Thanks to both of you "on it" guys.  I really appreciate the nitty gritty help.  I think I just found the problem I stumbled over.  I think that error message was actually very direct.  There is no WB.Worksheet(0).  The first worksheet is WB.Worksheet(1).   I will see where I get now with Archie's original suggestion.  Failing that I'll look at the example Archie sent and that doesn't straighten me out I'll have a look at the EPPlus possibility.  Thanks so much.

5

Open Discussion / Re: Control Logix array to Excel in 2024

« on: November 08, 2024, 12:34:53 PM »

Well, spelling "Worksheet" without an "s" did get me to the point where I can build it.  When I run it is still gives an error as if my workbook doesn't contain a zeroeth worksheet even after I purportedly added one.  The debugger breaks on the line that tries to assign a "New value" in cell A2 saying:

System.ArgumentException: 'There isn't a worksheet associated with that position.'

I assume that the "position" referred to is element zero of Worksheet array but I'm not seeing what I did wrong.  Sorry for my inexperience.  It's probably something obvious.  I have:

    Dim WB As New ClosedXML.Excel.XLWorkbook()

    WB.AddWorksheet()

    WB.Worksheet(0).Cell("A2").Value = "New Value"
    WB.SaveAs(".\ExcelFile2.xlsx")
End Sub

6

Open Discussion / Re: Control Logix array to Excel in 2024

« on: November 08, 2024, 11:49:13 AM »

Ooooh.  Thanks for the sharp eye, Bachphi.

7

Open Discussion / Re: Control Logix array to Excel in 2024

« on: November 08, 2024, 09:50:23 AM »

Archie,

Your instructions were delightfully detailed and easy to follow, but I must have screwed up somewhere.  When I build the project I get errors.  I know that the code inserted into MainForm is exactly as you suggested because I copied and pasted it in, so I'm not sending the code with this post.  The error I get is this:

Severity   Code   Description   Project   File   Line   Suppression State
Error (active)   BC32016   'Public Overloads ReadOnly Property Worksheets As IXLWorksheets' has no parameters and its return type cannot be indexed.   AdvancedHMI   C:\Users\phdel\source\repos\ArrayToExcel\AdvancedHMI\MainForm.vb   39

and then several warnings about vulnerabilities like this one:

Severity   Code   Description   Project   File   Line   Suppression State
Warning   NU1903   Package 'System.IO.Packaging' 8.0.0 has a known high severity vulnerability, https://github.com/advisories/GHSA-qj66-m88j-hmgj   AdvancedHMI   AdvancedHMI      


   

8

Open Discussion / Re: Control Logix array to Excel in 2024

« on: November 07, 2024, 09:21:50 PM »

Thanks so much to both Archie and bachpi for the super prompt replies!  This looks tractable!!  I will try it out this weekend and report back.

9

Open Discussion / Control Logix array to Excel in 2024

« on: November 06, 2024, 01:40:13 PM »

Hi all.  I'm a newbie and stumbled onto AdvancedHMI looking for a way outside of using RS-Linx to transfer the contents of a floating point array to an excel spreadsheet for further analysis.  I have seen some discussion in this forum in 2017 that's quite apropos though the details of the coding go a bit over my head.  I have downloaded the basic sample project and got live data to show up in the Digital Panel Meter.

What I'm wondering is if there's a sample project more recent than "KeepingRecordsFromCLXToExcelV399x" that comes closer to what I'm trying to do.

Ideally I would push a button or check a box in my excel workbook and AdvancedHMI would either write a fresh copy of the array values into my workbook or alternatively would write the array values as a spreadsheet or a comma limited text file which the workbook could refer to.  Another option would be for AdvancedHMI to write the array values to and SQL database which the spreadsheet could draw from.

I'm not utterly lost coding in Visual Basic but when I look at the back-and-forth Archie had with folks who were trying to accomplish what I was trying to accomplish it looked like I would have to do a great deal of side reading to understand it.  If there's a post-2017 solution that's closer to what I'm trying to do it would be great.