EPPlus Syntax

[bachphi]

If you are working with 5/03, what about DH485 port, the 1747-uic usb converter is not that expensive

[AabeckControls]

I have a UIC, but don't usually carry that with the laptop I am using for this project. My car would be full with 4 laptop cases and 2 more cases of cables and adapters. Not to mention the toolbox of cables and adapters for VFD's and servo controllers.

Now I think maybe the question I get asked a lot that "It's a PLC and you have PLC software so why don't you connect RSLogix to this Blaheuski Brothers PLC and make the changes we need?"  One program and a universal cable - that would be ideal.

[bachphi]

lol, No  .. but I would ask why would u need 4 laptops? what about VM?

[Godra]

You should try commenting out this line:

SerialDF1forSLCMicroCom1.Write(BasicLabel4.PLCAddressValue, "0")

since all the code is inside BasicLabel4.ValueChanged event.

Or maybe shift that line to the end.

[AabeckControls]

Bachphi,

4 laptops have different software, one has the PC 5512 card for Siemens (which a Siemens programmer told me not to get rid of as the 5512 card does things the USB adapter doesn't do.) (plus, him working for Siemens and only on Siemens - he carries 6 laptops around always!)  And 2 of the laptops have multiple VM's (XP, XP-Black, 7-Ultimate, 7-Pro).
 
Godra,

That line was at the end of the Sub, I just moved it up to see if the Write worked. I did move it back after copying the code and posting it.

[AabeckControls]

I found the problem in creating the file was the formatting of the headers. I deleted those sections and the file is created with the header info correctly, and a worksheet called Log.

For the Write data section I found that:
         Workbook.Worksheet.Cells(rowNumber, 1).Value =
has to be:
        ExcelPackage.Workbook.Worksheets(1).Cells(rowNumber, 1).Value =

That gets the first line of data written, and the columns do AutoFit their width correctly.

However, even after trying to move rowNumber += 1 to before the 'Try' it will only write the first minute's data. It does not overwrite the data each minute, as if the rowNumber was not incrementing. The write of "0" to the value does happen every time through.

Another thing I realized today (I saw it earlier but didn't pay attention to it) is now that I have to keep the Log screen's Comm's running in the background, I frequently get random "Object is not referenced....." error on the other screens because the driver is tied up.

Code: [Select]

Private Sub BasicLabel4_ValueChanged(sender As Object, e As EventArgs) Handles BasicLabel4.ValueChanged
        If BasicLabel4.Value = "True" Then

            'Add one row and start add the data               
            rowNumber += 1

           Dim fileName As String = (SerialDF1forSLCMicroCom1.Read("F25:99") + "-" + SerialDF1forSLCMicroCom1.Read("F8:33") + "-" + SerialDF1forSLCMicroCom1.Read("F8:47") + ".xlsx")
           
            Try
                Using ExcelPackage As New OfficeOpenXml.ExcelPackage(New IO.FileInfo(".\Logs\" + fileName))

                    ExcelPackage.Workbook.Worksheets(1).Cells(rowNumber, 1).Value = SerialDF1forSLCMicroCom1.Read("F8:48")
                    ExcelPackage.Workbook.Worksheets(1).Cells(rowNumber, 2).Value = SerialDF1forSLCMicroCom1.Read("F8:50")
                    ExcelPackage.Workbook.Worksheets(1).Cells(rowNumber, 3).Value = SerialDF1forSLCMicroCom1.Read("F25:99")
                    ExcelPackage.Workbook.Worksheets(1).Cells(rowNumber, 4).Value = SerialDF1forSLCMicroCom1.Read("F25:0")
                    ExcelPackage.Workbook.Worksheets(1).Cells(rowNumber, 5).Value = SerialDF1forSLCMicroCom1.Read("N10:2")
                    ExcelPackage.Workbook.Worksheets(1).Cells(rowNumber, 6).Value = SerialDF1forSLCMicroCom1.Read("N10:1")
                    ExcelPackage.Workbook.Worksheets(1).Cells(rowNumber, 7).Value = SerialDF1forSLCMicroCom1.Read("F8:0")
                    ExcelPackage.Workbook.Worksheets(1).Cells(rowNumber, 8).Value = SerialDF1forSLCMicroCom1.Read("F8:1")
                    ExcelPackage.Workbook.Worksheets(1).Cells(rowNumber, 9).Value = SerialDF1forSLCMicroCom1.Read("F8:37")
                    ExcelPackage.Workbook.Worksheets(1).Cells(rowNumber, 10).Value = SerialDF1forSLCMicroCom1.Read("N10:40")
                    ExcelPackage.Workbook.Worksheets(1).Cells(rowNumber, 11).Value = SerialDF1forSLCMicroCom1.Read("N10:41")
                    ExcelPackage.Workbook.Worksheets(1).Cells(rowNumber, 12).Value = SerialDF1forSLCMicroCom1.Read("N10:71")
                    ExcelPackage.Workbook.Worksheets(1).Cells(rowNumber, 13).Value = SerialDF1forSLCMicroCom1.Read("N10:42")
                    ExcelPackage.Workbook.Worksheets(1).Cells(rowNumber, 14).Value = SerialDF1forSLCMicroCom1.Read("N10:43")
                    ExcelPackage.Workbook.Worksheets(1).Cells(rowNumber, 15).Value = SerialDF1forSLCMicroCom1.Read("N10:72")
                    ExcelPackage.Workbook.Worksheets(1).Cells(rowNumber, 16).Value = SerialDF1forSLCMicroCom1.Read("N10:44")
                    ExcelPackage.Workbook.Worksheets(1).Cells(rowNumber, 17).Value = SerialDF1forSLCMicroCom1.Read("N10:45")
                    ExcelPackage.Workbook.Worksheets(1).Cells(rowNumber, 18).Value = SerialDF1forSLCMicroCom1.Read("N10:73")
                    ExcelPackage.Workbook.Worksheets(1).Cells(rowNumber, 19).Value = SerialDF1forSLCMicroCom1.Read("N10:46")
                    ExcelPackage.Workbook.Worksheets(1).Cells(rowNumber, 20).Value = SerialDF1forSLCMicroCom1.Read("N10:47")
                    ExcelPackage.Workbook.Worksheets(1).Cells(rowNumber, 21).Value = SerialDF1forSLCMicroCom1.Read("N10:74")
                    ExcelPackage.Workbook.Worksheets(1).Cells(rowNumber, 22).Value = SerialDF1forSLCMicroCom1.Read("N10:48")

                    'Fit the columns according to its content
                    ExcelPackage.Workbook.Worksheets(1).Column(1).AutoFit()
                    ExcelPackage.Workbook.Worksheets(1).Column(2).AutoFit()
                    ExcelPackage.Workbook.Worksheets(1).Column(3).AutoFit()
                    ExcelPackage.Workbook.Worksheets(1).Column(4).AutoFit()
                    ExcelPackage.Workbook.Worksheets(1).Column(5).AutoFit()
                    ExcelPackage.Workbook.Worksheets(1).Column(6).AutoFit()
                    ExcelPackage.Workbook.Worksheets(1).Column(7).AutoFit()
                    ExcelPackage.Workbook.Worksheets(1).Column(8).AutoFit()
                    ExcelPackage.Workbook.Worksheets(1).Column(9).AutoFit()
                    ExcelPackage.Workbook.Worksheets(1).Column(10).AutoFit()
                    ExcelPackage.Workbook.Worksheets(1).Column(11).AutoFit()
                    ExcelPackage.Workbook.Worksheets(1).Column(12).AutoFit()
                    ExcelPackage.Workbook.Worksheets(1).Column(13).AutoFit()
                    ExcelPackage.Workbook.Worksheets(1).Column(14).AutoFit()
                    ExcelPackage.Workbook.Worksheets(1).Column(15).AutoFit()
                    ExcelPackage.Workbook.Worksheets(1).Column(16).AutoFit()
                    ExcelPackage.Workbook.Worksheets(1).Column(17).AutoFit()
                    ExcelPackage.Workbook.Worksheets(1).Column(18).AutoFit()
                    ExcelPackage.Workbook.Worksheets(1).Column(19).AutoFit()
                    ExcelPackage.Workbook.Worksheets(1).Column(20).AutoFit()
                    ExcelPackage.Workbook.Worksheets(1).Column(21).AutoFit()
                    ExcelPackage.Workbook.Worksheets(1).Column(22).AutoFit()

                    'save our New workbook And we are done!
                    ExcelPackage.Save()

                End Using

            Catch ex As Exception
                Dim StatusLabel As String = "Status: Error = " & ex.Message

            End Try

            '***********************************************************************
            '* Clear the Write-Data bit which is monitored with the BasicLabel4
            '***********************************************************************
            SerialDF1forSLCMicroCom1.Write(BasicLabel4.PLCAddressValue, "0")

        End If

    End Sub


[AabeckControls]

Update:

The code above works perfectly. While testing I was changing a number while the press was not in cycle so I could see the changed numbers in the excel file. What I didn't realize was I was changing the Job# to see that change, but fileName uses the job number to look for the file name - so it didn't see a file with that number and ended the Try.

After realizing this I left the job # alone and changed other data and it wrote the data in the next row every time.

Thanks for all the help BachPhi.