read values from slc500, write to excel file

[John_R]

Hey All,

complete newb here, bear with me.....

got the software set up and working.

built a quick little project as referenced
set a com driver = EthernetIPforSLCMicroCom1 - set to IP in PLC
added 3 DigitalPanelMeter - set them to read "N" values in PLC

that all seems straight forward

I can run the project and the values I want pop in the displays.

So far, so good...
I'm starting off with baby steps and made my first two, now I want to take bigger steps.

My Objective here is to make a project that will read values from the PLC and write them to an Excel file, adding a row for each read, each minute, and creating a new file each day.

I would like to accomplish this without modifying the PLC program if possible.

Not asking too much am I?

I've been looking at examples for writing Excel files on the forum (found what looks like a good thread, 1875, kinda close to what I want, although it looks like it's for compactlogix using tags). I'm not sure if tailoring the OPs code for Datasubscriber would work for me, or if should I start from scratch?

In his example, it looks like the OP is triggering his read/write call from a bit in the PLC, as I said, would rather not have to modify the PLC program, is there a clock function in AHMI that would allow me to trigger from the project?

Or is there another way to go about this?

Can anyone point me in the right direction?

Regards,
JohnR

[Godra]

Here is a possible approach by using the Timer component found in the Toolbox under "All Windows Forms":

Use instructions from the thread you mentioned to install the EPPLUS package.

Drop a Timer on the form, set its Interval property to 60000 (which is 1 minute) and also set its Enabled property to True.

Double-click the Timer component to get to its Tick event code page and use the code similar to the one below:

Code: [Select]

    Private currentRow As Integer = 1
    Private PreviousDate As String = DateTime.Now.ToString("dd-MMM-yyyy")
    Private CurrentDate As String = ""
    Private Sub Timer1_Tick(sender As Object, e As EventArgs) Handles Timer1.Tick
        '* Check the current date for the new daily file creation
        CurrentDate = DateTime.Now.ToString("dd-MMM-yyyy")
        If PreviousDate <> CurrentDate Then
            If IO.File.Exists(".\MeterData.xlsx") Then
                IO.File.Move(".\MeterData.xlsx", ".\MeterData - " & PreviousDate & ".xlsx") '* Move the current file to a new file, preserve the name and include the Date in it
                IO.File.Delete(".\MeterData.xlsx") '* Delete the current file so it can be re-created
                PreviousDate = CurrentDate
                currentRow = 1
            End If
        End If

        Using ExcelPackage As New OfficeOpenXml.ExcelPackage(New IO.FileInfo(".\MeterData.xlsx"))

            '* Check if worksheet exists
            Dim sheetExists As Boolean
            For Each sheet In ExcelPackage.Workbook.Worksheets
                If sheet.Name = "test" Then
                    sheetExists = True
                    Exit For
                End If
            Next sheet

            If Not sheetExists Then
                ExcelPackage.Workbook.Worksheets.Add("test")
            End If

            If currentRow = 1 Then
                '*Write the Headers in Row 1
                ExcelPackage.Workbook.Worksheets(1).Cells(currentRow, 1).Value = "Date/Time"
                ExcelPackage.Workbook.Worksheets(1).Cells(currentRow, 2).Value = "Meter 1"
                ExcelPackage.Workbook.Worksheets(1).Cells(currentRow, 3).Value = "Meter 2"
                ExcelPackage.Workbook.Worksheets(1).Cells(currentRow, 4).Value = "Meter 3"
            End If

            currentRow += 1

            '* Insert a timestamp and transfer the current values of the meters to Excel
            Dim ws As OfficeOpenXml.ExcelWorksheet = ExcelPackage.Workbook.Worksheets(1)
            ws.Cells(currentRow, 1).Value = DateTime.Now.ToString("dd-MMM-yyyy HH:mm:ss")
            ws.Cells(currentRow, 2).Value = Me.DigitalPanelMeter1.Value
            ws.Cells(currentRow, 3).Value = Me.DigitalPanelMeter2.Value
            ws.Cells(currentRow, 4).Value = Me.DigitalPanelMeter3.Value

            ExcelPackage.Save()

        End Using
    End Sub

The output should look like the attached picture, where the alignment might need some adjustments.

The code seems to be working fine for creating a new daily file.

The Timer component is not perfect and might eventually show as inaccurate since it could get affected by other applications running on the same computer.

If you run the app for a while and then stop it and run it again, the values in Excel file should get overwritten.

[John_R]

Thank you Godra, that got me going in the right direction. JohnR