Controlling Excel with EPPLUS

[bob1371]

I've been collecting data now for a couple of weeks and all is working good. The current project is copying 7 array (500 elements in each) to the file. I am using a string tag (MyValues20) with mmddyyyy as well as shift "1st" and this is the name of the tab. I  save  this data each shift, clear the array and start over. As long as the value is MyValues20 is different than last it creates a new tab and writes the data. This has been working great.

Code: [Select]

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


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

I've moved on to a smaller application only collecting data from 7 tags twice daily.  Instead of creating a new tab I want to just index to the next row and let it run for the month but as long as MyValues20 is same as existing tab i get an exception error.

Do I need to ignore this line "ExcelPackage.Workbook.Worksheets.Add(MyValues20)" if the tab already exist?

Thanks.

[Archie]

Try something like this:

Code: [Select]

        Using ExcelPackage As New OfficeOpenXml.ExcelPackage(New IO.FileInfo("C:\Test.xlsx"))
            Dim ws As OfficeOpenXml.ExcelWorksheet = ExcelPackage.Workbook.Worksheets(MyValues20)
            If ws Is Nothing Then
                ws = ExcelPackage.Workbook.Worksheets.Add(MyValues20)
            End If

             For Index = 0 To MyValues0.Length - 1
                 Dim CellNum As String = "A" & (Index + 2)
                 ws.Cells(CellNum).Value = MyValues0(Index)
             Next


[bob1371]

Thanks Archie,

Made these changes and a couple other things and working great.