AdvancedHMI Software
General Category => Support Questions => Topic started by: bob1371 on July 09, 2018, 12:01:30 AM
-
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.
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.
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
-
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
-
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"
-
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.
-
To check if the worksheet already exists, you could use this code:
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
-
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.