Bachphi,
I think I have everything as you suggested, but it still won't create the file.
(I just thought tomorrow I will change it to a set filename and put and existing file on the drive before I open AHMI as a test to see if it is trying to write the data to it)
Another question - could the 3 entries 'Private Sub BasicLabel7_Click' before the '_valueChanged' be interfering and should they be remarked out?
Imports OfficeOpenXml
Public Class _5_Logs
Private Sub SerialDF1forSLCMicroCom1_DataReceived(sender As Object, e As Drivers.Common.PlcComEventArgs) Handles SerialDF1forSLCMicroCom1.DataReceived
End Sub
Private Workbook As Object
Dim fileName As String
'Keep track of the row that we're on, but start with three to skip the header
Dim rowNumber As Integer = 3
Private Sub BasicDataLogger21_DataChanged(sender As Object, e As Drivers.Common.PlcComEventArgs) Handles BasicDataLogger21.DataChanged
End Sub
Private Sub Me_FormClosing(sender As Object, e As FormClosingEventArgs) Handles Me.FormClosing
MainForm.Show()
End Sub
Private Sub _5_Logs_Load(sender As Object, e As EventArgs) Handles MyBase.Load
End Sub
Private Sub BasicLabel7_Click(sender As Object, e As EventArgs) Handles BasicLabel7.Click
'*******************************************************************************
'* BasicLabel7 controls creation of Excel log file
'* At end of create code (below) clears the value
'*******************************************************************************
End Sub
Private Sub BasicLabel7_ValueChanged(sender As Object, e As EventArgs) Handles BasicLabel7.ValueChanged
If BasicLabel7.Value = "True" Then
'Create file named: Log_[date]_[time-started]_[job-number].xlsx
Dim fileName As String = "Log_" + SerialDF1forSLCMicroCom1.Read("F8:33") + "_" + SerialDF1forSLCMicroCom1.Read("F8:47") + "_" + SerialDF1forSLCMicroCom1.Read("F25:99") + ".xlsx"
Try
'Create the file using the FileInfo object
Using ExcelPackage As New ExcelPackage(New System.IO.FileInfo(".\" + fileName))
'add a New worksheet to the empty workbook
Dim worksheet As ExcelWorksheet = ExcelPackage.Workbook.Worksheets.Add("Log")
'--------- Data And styling goes here -------------- //
'Add some formatting to the worksheet
worksheet.DefaultRowHeight = 14
worksheet.HeaderFooter.FirstFooter.LeftAlignedText = String.Format("Generated: {0}", DateTime.Now.ToShortDateString())
worksheet.Row(1).Height = 20
worksheet.Row(2).Height = 20
worksheet.Row(3).Height = 18
'Start adding the header
'First of all the first row
worksheet.Cells(1, 1).Value = "Ivanova726"
worksheet.Cells(1, 2).Value = "Romulus, MI, USA"
'Add the second row of header data
worksheet.Cells(2, 1).Value = "Lamination Log"
worksheet.Cells(2, 2).Value = "Press 20066"
'Ok now format the first row of the header, but only the first two columns
Dim Aabeck = worksheet.Cells(1, 1, 2, 1)
Aabeck.Style.Font.Bold = True
Aabeck.Style.Fill.BackgroundColor.SetColor(Color.Black)
Aabeck.Style.Font.Color.SetColor(Color.WhiteSmoke)
Aabeck.Style.ShrinkToFit = False
'Ok now format the second row of the header, but only the first two columns
Dim Aabeck2 = worksheet.Cells(2, 1, 2, 2)
Aabeck2.Style.Font.Bold = True
Aabeck2.Style.Fill.BackgroundColor.SetColor(Color.Yellow)
Aabeck2.Style.Font.Color.SetColor(Color.Black)
Aabeck2.Style.ShrinkToFit = False
'Add the data hea
worksheet.Cells(3, 1).Value = "Date"
worksheet.Cells(3, 2).Value = "Time"
worksheet.Cells(3, 3).Value = "Job #"
worksheet.Cells(3, 4).Value = "Recipe"
worksheet.Cells(3, 5).Value = "RunTime"
worksheet.Cells(3, 6).Value = "Step #"
worksheet.Cells(3, 7).Value = "Pressure SP"
worksheet.Cells(3, 8).Value = "Pressure Actual"
worksheet.Cells(3, 9).Value = "Vacuum Actual"
worksheet.Cells(3, 10).Value = "Temperature SP"
worksheet.Cells(3, 11).Value = "Temp - Top Platen"
worksheet.Cells(3, 12).Value = "Temp - Book 1"
worksheet.Cells(3, 13).Value = "Temp - Platen 1 Top"
worksheet.Cells(3, 14).Value = "Temp - Platen 1 Bottom"
worksheet.Cells(3, 15).Value = "Temp - Book 2"
worksheet.Cells(3, 16).Value = "Temp - Platen 2 Top"
worksheet.Cells(3, 17).Value = "Temp - Platen 2 Bottom"
worksheet.Cells(3, 18).Value = "Temp - Book 3"
worksheet.Cells(3, 19).Value = "Temp - Platen 3 Top"
worksheet.Cells(3, 20).Value = "Temp - Platen 3 Bottom"
worksheet.Cells(3, 21).Value = "Temp - Book 4"
worksheet.Cells(3, 22).Value = "Temp - Bottom Platen"
'save our New workbook And we are done!
ExcelPackage.Save()
'* Clear the Write-Data bit which is monitored with the BasicLabel7
SerialDF1forSLCMicroCom1.Write(BasicLabel7.PLCAddressValue, "0")
End Using
Catch ex As Exception
Dim StatusLabel As String = "Status: Error = " & ex.Message
End Try
End If
End Sub
Private Sub BasicLabel4_Click(sender As Object, e As EventArgs) Handles BasicLabel4.Click
'*******************************************************************************
'* BasicLabel4 controls write of data to Excel log file
'* At end of write code (below) clears the value
'*******************************************************************************
End Sub
Private Sub BasicLabel4_ValueChanged(sender As Object, e As EventArgs) Handles BasicLabel4.ValueChanged
If BasicLabel4.Value = "True" Then
Dim fileName As String = "Log_" + SerialDF1forSLCMicroCom1.Read("F8:33") + "_" + SerialDF1forSLCMicroCom1.Read("F8:47") + "_" + SerialDF1forSLCMicroCom1.Read("F25:99") + ".xlsx"
Try
Using ExcelPackage As New ExcelPackage(New IO.FileInfo(".\" + fileName))
'Add one row and start add the data
rowNumber += 1
Workbook.worksheet.Cells(1, rowNumber).Value = SerialDF1forSLCMicroCom1.Read("F8:48")
Workbook.worksheet.Cells(2, rowNumber).Value = SerialDF1forSLCMicroCom1.Read("F8:50")
Workbook.worksheet.Cells(3, rowNumber).Value = SerialDF1forSLCMicroCom1.Read("F25:99")
Workbook.worksheet.Cells(4, rowNumber).Value = SerialDF1forSLCMicroCom1.Read("F25:0")
Workbook.worksheet.Cells(5, rowNumber).Value = SerialDF1forSLCMicroCom1.Read("N10:2")
Workbook.worksheet.Cells(6, rowNumber).Value = SerialDF1forSLCMicroCom1.Read("N10:1")
Workbook.worksheet.Cells(7, rowNumber).Value = SerialDF1forSLCMicroCom1.Read("F8:0")
Workbook.worksheet.Cells(8, rowNumber).Value = SerialDF1forSLCMicroCom1.Read("F8:1")
Workbook.worksheet.Cells(9, rowNumber).Value = SerialDF1forSLCMicroCom1.Read("F8:37")
Workbook.worksheet.Cells(10, rowNumber).Value = SerialDF1forSLCMicroCom1.Read("N10:40")
Workbook.worksheet.Cells(11, rowNumber).Value = SerialDF1forSLCMicroCom1.Read("N10:41")
Workbook.worksheet.Cells(12, rowNumber).Value = SerialDF1forSLCMicroCom1.Read("N10:71")
Workbook.worksheet.Cells(13, rowNumber).Value = SerialDF1forSLCMicroCom1.Read("N10:42")
Workbook.worksheet.Cells(14, rowNumber).Value = SerialDF1forSLCMicroCom1.Read("N10:43")
Workbook.worksheet.Cells(15, rowNumber).Value = SerialDF1forSLCMicroCom1.Read("N10:72")
Workbook.worksheet.Cells(16, rowNumber).Value = SerialDF1forSLCMicroCom1.Read("N10:44")
Workbook.worksheet.Cells(17, rowNumber).Value = SerialDF1forSLCMicroCom1.Read("N10:45")
Workbook.worksheet.Cells(18, rowNumber).Value = SerialDF1forSLCMicroCom1.Read("N10:73")
Workbook.worksheet.Cells(19, rowNumber).Value = SerialDF1forSLCMicroCom1.Read("N10:46")
Workbook.worksheet.Cells(20, rowNumber).Value = SerialDF1forSLCMicroCom1.Read("N10:47")
Workbook.worksheet.Cells(21, rowNumber).Value = SerialDF1forSLCMicroCom1.Read("N10:74")
Workbook.worksheet.Cells(22, rowNumber).Value = SerialDF1forSLCMicroCom1.Read("N10:48")
'Fit the columns according to its content
Workbook.worksheet.Column(1).AutoFit()
Workbook.worksheet.Column(2).AutoFit()
Workbook.worksheet.Column(3).AutoFit()
Workbook.worksheet.Column(4).AutoFit()
Workbook.worksheet.Column(5).AutoFit()
Workbook.worksheet.Column(6).AutoFit()
Workbook.worksheet.Column(7).AutoFit()
Workbook.worksheet.Column(8).AutoFit()
Workbook.worksheet.Column(9).AutoFit()
Workbook.worksheet.Column(10).AutoFit()
Workbook.worksheet.Column(11).AutoFit()
Workbook.worksheet.Column(12).AutoFit()
Workbook.worksheet.Column(13).AutoFit()
Workbook.worksheet.Column(14).AutoFit()
Workbook.worksheet.Column(15).AutoFit()
Workbook.worksheet.Column(16).AutoFit()
Workbook.worksheet.Column(17).AutoFit()
Workbook.worksheet.Column(18).AutoFit()
Workbook.worksheet.Column(19).AutoFit()
Workbook.worksheet.Column(20).AutoFit()
Workbook.worksheet.Column(21).AutoFit()
Workbook.worksheet.Column(22).AutoFit()
'save our New workbook And we are done!
ExcelPackage.Save()
'***********************************************************************
'* Clear the Write-Data bit which is monitored with the BasicLabel4
'***********************************************************************
SerialDF1forSLCMicroCom1.Write(BasicLabel4.PLCAddressValue, "0")
End Using
Catch ex As Exception
Dim StatusLabel As String = "Status: Error = " & ex.Message
End Try
End If
End Sub
Private Sub BasicLabel6_Click(sender As Object, e As EventArgs) Handles BasicLabel6.Click
'*******************************************************************************
'* BasicLabel6 controls closing of Excel log file
'* At end of write code (below) clears the value
'*******************************************************************************
End Sub
Private Sub BasicLabel6_ValueChanged(sender As Object, e As EventArgs) Handles BasicLabel6.ValueChanged
If BasicLabel6.Value = "True" Then
Dim fileName As String = "Log_" + SerialDF1forSLCMicroCom1.Read("F8:33") + "_" + SerialDF1forSLCMicroCom1.Read("F8:47") + "_" + SerialDF1forSLCMicroCom1.Read("F25:99") + ".xlsx"
Try
Using ExcelPackage As New ExcelPackage(New System.IO.FileInfo(".\" + fileName))
'Set some properties for the file
Workbook.Properties.Title = "Lamination Log"
Workbook.Properties.Subject = "Vacuum Press Cycle Log"
Workbook.Properties.Company = "Saturn Flex Systems"
Workbook.Properties.Author = "Ivanova726"
Workbook.Properties.Created = DateTime.Now
'save our New workbook And we are done!
ExcelPackage.Save()
'***********************************************************************
'* Clear the Close File bit which is monitored with the BasicLabel6
'***********************************************************************
SerialDF1forSLCMicroCom1.Write(BasicLabel6.PLCAddressValue, "0")
End Using
Catch ex As Exception
Dim StatusLabel As String = "Status: Error = " & ex.Message
End Try
End If
End Sub
End Class