Copy 500 element array to Excel?

[bob1371]

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.

[Archie]

Here is another resource for useful tutorials:

http://advancedhmi.com/forum/index.php?topic=298.msg1056#msg1056

[bob1371]

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.

[Archie]

After you added the lines of code ("Console.WriteLine..."), do you see anything in your Output Window when you run the application?

Add a BasicLabel to the form and set PLCAddressValue to the same tag name you put in the DataSubscriber.

[bob1371]

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.

[Archie]

Exception Thrown: 'System.IndexOutOfRangeException' in EPPlus.dll

This typically happens when referencing a cell or sheet 0 with EPPlus

I did happen to notice a mistake in my code. It should be this:

ExcelPackage.Workbook.Worksheets(1).Cells(1, I + 1).Value = MyValues(I)

[DougLyons]

Based on Archie's comments and looking at the code, it appears that you have probably used the letter "I" instead of the number "one" somewhere. Its unfortunate that these look so much alike. Here is another version of the line with verbal comments that may help you.

ExcelPackage.Workbook.Worksheets(number one).Cells(number one, Letter "I" + number one).Value = MyValues(Letter "I")

[bob1371]

Thanks Archie and Doug.

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

[Archie]

To help ensure there is no 1 and I confusion, change the variable name to something more meaningful. And also break it down more to help narrow the problem.

Code: [Select]

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")
    ws.Cells(1, Index+1).Value = MyValues(Index)
Next


[bob1371]

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.

[bob1371]

+++++++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....

[bob1371]

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


[bob1371]

I'm Back. LOL


I've modified the code to collect data from all 8 cells with the goal to create the .xlsx file first of the month then write the data to a different tab each day. I'm writing the date as tab name (yyyy mm dd) and headers for the columns I'm using. However when the date rolls over it creates the new tab with the correct date but doesn't write the data to it. I've tried various ways to mess around with this "ExcelPackage.Workbook.Worksheets(1)" but no luck. Can you point me in the right direction???

Also is it possible to get a confirmation bit back to PLC that data was written??

The code and .xlsx file attached.

Thanks.

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 MyValues0() As String = EthernetIPforCLXCom1.Read("VCell1B_FES_Cycle_Average[0]", 500)
            Dim MyValues1() As String = EthernetIPforCLXCom1.Read("VCell_1A_FES_Cycle_Average[0]", 500)
            Dim MyValues2() As String = EthernetIPforCLXCom1.Read("VCell_2B_FES_Cycle_Average[0]", 500)
            Dim MyValues3() As String = EthernetIPforCLXCom1.Read("VCell_2A_FES_Cycle_Average[0]", 500)
            Dim MyValues4() As String = EthernetIPforCLXCom1.Read("VCell_3B_FES_Cycle_Average[0]", 500)
            Dim MyValues5() As String = EthernetIPforCLXCom1.Read("VCell_3A_FES_Cycle_Average[0]", 500)
            Dim MyValues6() As String = EthernetIPforCLXCom1.Read("Inspect_FES_Cycle_Average[0]", 500)
            Dim MyValues7() As String = EthernetIPforCLXCom1.Read("Repair_FES_Cycle_Average[0]", 500)
            Dim MyValues8 As String = EthernetIPforCLXCom1.Read("MDY5")

            Console.WriteLine(MyValues0.Length & "elements read VCell_1B.")
            Console.WriteLine(MyValues1.Length & "elements read VCell_1A.")
            Console.WriteLine(MyValues2.Length & "elements read VCell_2B.")
            Console.WriteLine(MyValues3.Length & "elements read VCell_2A.")
            Console.WriteLine(MyValues4.Length & "elements read VCell_3B.")
            Console.WriteLine(MyValues5.Length & "elements read VCell_3A.")
            Console.WriteLine(MyValues6.Length & "elements read Inspect.")
            Console.WriteLine(MyValues7.Length & "elements read Repair.")
            Console.WriteLine("All Data Read")

            '*Transfer the values to Excel

            '*Creates Excel file
            Using ExcelPackage As New OfficeOpenXml.ExcelPackage(New IO.FileInfo("C:\Data.xlsx"))
                ExcelPackage.Workbook.Worksheets.Add(MyValues8)
                Console.WriteLine("ws creation ok")

                '*Writes the Headers in Row 1
                ExcelPackage.Workbook.Worksheets(1).Cells("A1").Value = "VCell 1B"
                ExcelPackage.Workbook.Worksheets(1).Cells("C1").Value = "VCell 1A"
                ExcelPackage.Workbook.Worksheets(1).Cells("E1").Value = "VCell 2B"
                ExcelPackage.Workbook.Worksheets(1).Cells("G1").Value = "VCell 2A"
                ExcelPackage.Workbook.Worksheets(1).Cells("I1").Value = "VCell 3B"
                ExcelPackage.Workbook.Worksheets(1).Cells("K1").Value = "VCell 3A"
                ExcelPackage.Workbook.Worksheets(1).Cells("M1").Value = "Inspect"
                ExcelPackage.Workbook.Worksheets(1).Cells("O1").Value = "Repair"
                Console.WriteLine("header creation ok")

                '*Loops to write data in correct columns
                For Index = 0 To MyValues0.Length - 1
                    Console.WriteLine("Element " & Index & "=" & MyValues0(Index))
                    Dim ws As OfficeOpenXml.ExcelWorksheet = ExcelPackage.Workbook.Worksheets(1)
                    Dim CellNum As String = "A" & (Index + 3)
                    ws.Cells(CellNum).Value = MyValues0(Index)
                Next
                Console.WriteLine("Worksheet OK 1B")

                For Index = 0 To MyValues1.Length - 1
                    Console.WriteLine("Element " & Index & "=" & MyValues1(Index))
                    Dim ws As OfficeOpenXml.ExcelWorksheet = ExcelPackage.Workbook.Worksheets(1)
                    Dim CellNum As String = "C" & (Index + 3)
                    ws.Cells(CellNum).Value = MyValues1(Index)
                Next
                Console.WriteLine("Worksheet OK 1A")

                For Index = 0 To MyValues2.Length - 1
                    Console.WriteLine("Element " & Index & "=" & MyValues2(Index))
                    Dim ws As OfficeOpenXml.ExcelWorksheet = ExcelPackage.Workbook.Worksheets(1)
                    Dim CellNum As String = "E" & (Index + 3)
                    ws.Cells(CellNum).Value = MyValues2(Index)
                Next
                Console.WriteLine("Worksheet OK 2B")

                For Index = 0 To MyValues3.Length - 1
                    Console.WriteLine("Element " & Index & "=" & MyValues3(Index))
                    Dim ws As OfficeOpenXml.ExcelWorksheet = ExcelPackage.Workbook.Worksheets(1)
                    Dim CellNum As String = "G" & (Index + 3)
                    ws.Cells(CellNum).Value = MyValues3(Index)
                Next
                Console.WriteLine("Worksheet OK 2A")

                For Index = 0 To MyValues4.Length - 1
                    Console.WriteLine("Element " & Index & "=" & MyValues4(Index))
                    Dim ws As OfficeOpenXml.ExcelWorksheet = ExcelPackage.Workbook.Worksheets(1)
                    Dim CellNum As String = "I" & (Index + 3)
                    ws.Cells(CellNum).Value = MyValues4(Index)
                Next
                Console.WriteLine("Worksheet OK 3B")

                For Index = 0 To MyValues5.Length - 1
                    Console.WriteLine("Element " & Index & "=" & MyValues5(Index))
                    Dim ws As OfficeOpenXml.ExcelWorksheet = ExcelPackage.Workbook.Worksheets(1)
                    Dim CellNum As String = "K" & (Index + 3)
                    ws.Cells(CellNum).Value = MyValues5(Index)
                Next
                Console.WriteLine("Worksheet OK 3A")
                For Index = 0 To MyValues6.Length - 1
                    Console.WriteLine("Element " & Index & "=" & MyValues6(Index))
                    Dim ws As OfficeOpenXml.ExcelWorksheet = ExcelPackage.Workbook.Worksheets(1)
                    Dim CellNum As String = "M" & (Index + 3)
                    ws.Cells(CellNum).Value = MyValues6(Index)
                Next
                Console.WriteLine("Worksheet OK Inspect")

                For Index = 0 To MyValues7.Length - 1
                    Console.WriteLine("Element " & Index & "=" & MyValues7(Index))
                    Dim ws As OfficeOpenXml.ExcelWorksheet = ExcelPackage.Workbook.Worksheets(1)
                    Dim CellNum As String = "O" & (Index + 3)
                    ws.Cells(CellNum).Value = MyValues7(Index)
                Next
                Console.WriteLine("Worksheet OK Repair")

                '*Saves.xlsl file
                ExcelPackage.Save()

            End Using
        End If
    End Sub


[bob1371]

Figured out this last issue.  I was using Concatenate to build yyyy/mm/dd I changed it to yyyy.mm.dd and working as it should.

[bob1371]

Archie,

For the projects I am working on I am using Panasonic Tough book with Win 7.

After creating I am loading to Mini PC which is running Win 10. Everything was working fine but when I got the data collection stuff worked out I added the datasubscriber  and copied my code. Everything works but doesn't create the file. I've tried on 2 different computers with Win 10 and same thing. I can run the same application on Win 7 and all is fine.

Any info on why this wouldn't work on Win10?

Thanks.