EPPLUS .dll error

[bob1371]

Hello,


Working on a project and can't seem to get rid of this error.

I've updated the EPPLUS to latest version.
I've looked back through my previous post but still no luck.
If anyone could point me in right direction I would appreciate it.

Working on laptop with Win7 Pro 32 bit.

Thanks.

From Debug output window.

Code: [Select]

Prepare to Read Data
Data Has Been Read
Exception thrown: 'System.ArgumentException' in EPPlus.dll
The thread 0xc70 has exited with code 0 (0x0).
The thread 0x21ec has exited with code 0 (0x0).
The thread 0x27d0 has exited with code 0 (0x0).
The program '[12080] AdvancedHMI.exe' has exited with code 0 (0x0).


Here's everything in the Datasubscriber.

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(value:="Prepare to Read Data")
            '* Sets MyValues tags equal to PLC Tags
            Dim MyValues0 As String = EthernetIPforCLXCom1.Read("Station1_OperatorLog_Serial")
            Dim MyValues1 As String = EthernetIPforCLXCom1.Read("Station1_OperatorLog_Name")
            Dim MyValues25 As String = EthernetIPforCLXCom1.Read("TimeStamp")
            Dim MyValues8 As String = EthernetIPforCLXCom1.Read("MYD10")

            Console.WriteLine(value:="Data Has Been Read")



            '*Create Excel File & Transfer the Values to Excel

            '*Creates Excel file
            Using ExcelPackage As New OfficeOpenXml.ExcelPackage(New IO.FileInfo("C:\Users\Owner\Desktop\AdvancedHMIv399x\OP1Login.xlsx"))
                ExcelPackage.Workbook.Worksheets.Add(MyValues8)


                Console.WriteLine("ws creation ok")

                '*Writes the Headers in Row 1
                ExcelPackage.Workbook.Worksheets(MyValues0).Cells("A1").Value = "Part Serial Number"
                ExcelPackage.Workbook.Worksheets(MyValues1).Cells("B1").Value = "Operator Name"
                ExcelPackage.Workbook.Worksheets(MyValues25).Cells("C1").Value = "Date / Time Stamp"

                Console.WriteLine("header creation ok")

                ExcelPackage.Workbook.Worksheets(MyValues8).Cells("A1").AutoFitColumns()
                ExcelPackage.Workbook.Worksheets(MyValues8).Cells("B1").AutoFitColumns()
                ExcelPackage.Workbook.Worksheets(MyValues8).Cells("C1").AutoFitColumns()

                '*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(MyValues8)
                    Dim CellNum As String = "A" & (Index + 3)
                    ws.Cells(CellNum).Value = MyValues0(Index)
                Next

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

                '*For Index = 0 To MyValues25.Length - 1
                '*Console.WriteLine("Element " & Index & "=" & MyValues25(Index))
                '*  Dim ws As OfficeOpenXml.ExcelWorksheet = ExcelPackage.Workbook.Worksheets(MyValues8)
                '*    Dim CellNum As String = "C" & (Index + 3)
                '*      ws.Cells(CellNum).Value = MyValues25(Index)
                '*  Next
                Console.WriteLine("Operator Login OK")



                '*Saves.xlsl file
                ExcelPackage.Save()

            End Using

        End If
    End Sub
End Class


[Godra]

If you check this link, it will explain when this exception is thrown:  https://msdn.microsoft.com/en-us/library/ms242197.aspx

It is described as: "An ArgumentException exception is thrown when at least one of the arguments provided to a method does not meet the specifications of the parameters of the method."

You could place a breakpoint on the line "Using ExcelPackage As New OfficeOpenXml.ExcelPackage..." and then step through the code with F11 too see at which line it will throw the exception.

To get familiar with using breakpoints, see this link:  http://www.visual-basic-tutorials.com/beginner/Breakpoints.html

[Godra]

Your code seems to be all wrong so check it out:

All your "MyValues" are declared as String, not as an array.
In your code, you are indexing them as if they were an array.

You are not checking if the worksheet already exists, you're always adding it (which should be fine if you are creating a new file every time e.Values(0) becomes True):
          ExcelPackage.Workbook.Worksheets.Add(MyValues8)

Even when you add it the code afterwards, for the header creation, is not pointing to that worksheet:
          ExcelPackage.Workbook.Worksheets(MyValues0).Cells("A1").Value = "Part Serial Number"
          ExcelPackage.Workbook.Worksheets(MyValues1).Cells("B1").Value = "Operator Name"
          ExcelPackage.Workbook.Worksheets(MyValues25).Cells("C1").Value = "Date / Time Stamp"

[bob1371]

Thanks Godra,

Things finally Slowing down tonight so  maybe get a little time to mess with this. I did several projects last year and were working great. I had them running off USB drives and saving data to excel. Others decided they needed the USB drives more than I did and it had the Solution Files on there.
I should have retain some of the work I did but hey I like to make it hard on myself.

[Godra]

To check if the worksheet already exists, you could use this code:

Code: [Select]

                Dim sheetExists As Boolean
                For Each sheet In ExcelPackage.Workbook.Worksheets
                    If sheet.Name = MyValues8 Then
                        sheetExists = True
                        Exit For
                    End If
                Next sheet

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

It was adopted from here:  https://stackoverflow.com/questions/6040164/excel-vba-if-worksheetwsname-exists

[Godra]

There is a post on the stackoverflow website, which might have been posted by you:

https://stackoverflow.com/questions/46400357/exception-thrown-system-indexoutofrangeexception-in-epplus-dll

It does state that the code was working so check the similarities with your current code.