AdvancedHMI Software
General Category => Support Questions => Topic started by: AabeckControls on August 16, 2018, 03:42:54 PM
-
I had a project put on hold over a year ago due to a management change at a customers. For it I started thread 1779 about logging data. Just this week it became an active task and I am having a couple issues. The code I presently have is attached.
I got it to not show any errors and rebuild successfully, and it does open on the target computer. However it does not create the .xlsx file or show any errors. I also have the code written to clear the ValueChange value - but it doesn't - it stays 1.
In a lot of examples I found there are differing syntax's for the same function. Here are a couple:
'Set the file name and get the output directory
var fileName = "Log-" + SerialDF1forSLCMicroCom1.Read("F8:48") + SerialDF1forSLCMicroCom1.Read("F8:48") + SerialDF1forSLCMicroCom1.Read("F25:99") + ".xlsx";
var outputDir = context.Server.MapPath(".\logs\");
'*Creates Excel file
Using ExcelPackage As New OfficeOpenXml.ExcelPackage(New IO.FileInfo("C:\Users\Owner\Desktop\AdvancedHMIv399x\OP1Login.xlsx"))
ExcelPackage.Workbook.Worksheets.Add(MyValues8)
But neither will work properly using VS Community 2017. The first example will not accept 'var' as a command - it tries to change it to varType, but that doesn't work either. Also, the first example setting the outputDir with 'context.Server.MapPath' shows that is not valid. Also VS will not accept the ';' at the end of each line.
I have tried fileName and outputDir in each section and at the start.
The second example won't take the line ExcelPackage.Workbook.Wor..., but does accept Workbook.Wor... as is in my code.
I also have multiple methods for addressing cells
using (var range = ExcelPackage.Workbook.worksheet.Cells[1, 1, 1, 2]) var is not accepted nor ExcelPackage, and says 'range' is a friend
Workbook.Worksheets(MyValues0).Cells("A1").Value = "Part Serial Number" This type of cell addressing is accepted also - do both these methods work ?
The computer is Windows 7 x64 with a serial port on the motherboard, the project is upgraded to AHMI v399x, the PLC is a SLC5/03 using DF1.
-
I have also tried different ways of defining the outputDir
'Set the output directory
Public outputDir As String = (".\Logs\")
'Public outputDir = context.Server.MapPath(".\logs\");
I have tried "Dim outputDir" and "Public outputDir" on both lines above - but the second line still stops the 'context.Server.MapPath'
I have also tried the outputDir as ".\Logs\", "\HMI _726_v399x\AdvancedHMI\bin\Debug\Logs\" and "C:\HMI _726_v399x\AdvancedHMI\bin\Debug\Logs\"
-
You are mixing c# and VB.
If your goal is to create a new excel file then try this:
Dim Filename As String = "Example" + DateAndTime.Now.ToString("yyyyMMdd") + ".xlsx"
Using Package As New ExcelPackage(New System.IO.FileInfo(".\" + Filename))
Dim worksheet As ExcelWorksheet = Package.Workbook.Worksheets.Add("Example " & DateTime.Now.ToShortDateString())
worksheet.Cells(1, 1).Value = "name"
worksheet.Cells(1, 2).Value = "Address"
worksheet.Cells(1, 3).Value = "Status (unstyled)"
worksheet.Cells(2, 1).Value = "Areobeck"
worksheet.Cells(2, 2).Value = "somewhere"
worksheet.Cells(2, 3).Value = "alive"
Package.Workbook.Properties.Title = "Hit list"
Package.Workbook.Properties.Author = "MynameisNobody"
Package.Workbook.Properties.Company = "Trinity"
Package.Save()
End Using
-
Bachphi,
Thanks for the help, I made the changes in the 2 sections to create and write the headers, except it wouldn't accept 'Package.Workbook.Properties', I had to change it to ExcelPackage.Workbook.Properties to clear the error. And the save line had to be ExcelPackage.Save().
But, in the section where I log the data each minute it wouldn't take worksheet.Cells(..., I had to leave that at Workbook.worksheet.Cells(…
Now I have a question addressing the cells using the variable rowNumber - are either of these correct?:
Workbook.worksheet.Cells(rowNumber, 1).Value = SerialDF1forSLCMicroCom1.Read("F8:48")
Workbook.worksheet.Cells([rowNumber], 2).Value = SerialDF1forSLCMicroCom1.Read("F8:50")
Is this correct to autofit the columns?:
Workbook.worksheet.Column(1).AutoFit()
Workbook.worksheet.Column(2).AutoFit()
-
goto the top of the form and add:
Imports OfficeOpenXml
If you are using "Using Excelpackage" then ofcourse you need to use Excelpackage, in my case , I defined it as package:
and you should be able to use the code.
Dim Filename As String = "Example" + DateAndTime.Now.ToString("yyyyMMdd") + ".xlsx"
Using Package As New ExcelPackage(New System.IO.FileInfo(".\" + Filename))
Dim worksheet As ExcelWorksheet = Package.Workbook.Worksheets.Add("Example " & DateTime.Now.ToShortDateString())
worksheet.Cells(1, 1).Value = "name"
worksheet.Cells(1, 2).Value = "Address"
worksheet.Cells(1, 3).Value = "Status (unstyled)"
worksheet.Cells(2, 1).Value = SerialDF1forSLCMicroCom1.Read("F8:48")
worksheet.Cells(2, 2).Value = SerialDF1forSLCMicroCom1.Read("F8:50")
worksheet.Cells(2, 3).Value = "alive"
Worksheet.Cells(Worksheet.Dimension.Address).AutoFitColumns()
Package.Workbook.Properties.Title = "Hit list"
Package.Workbook.Properties.Author = "MynameisNobody"
Package.Workbook.Properties.Company = "Trinity"
Package.Save()
End Using
-
bachphi,
Thanks.
I'm on the road today, hopefully I can get to this in the morning.
I'll let you know how it turns out.
-
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
-
I assume you know how to use Nuget to install EPPlus package.
Since you are not using label click event you can remove it.
Simplify your program to one basic label and make it works first, try focus on your BasicLabel7 first
I assume PLCAddress value of your label is Boolean bit type and you can toggle it on at will?
Set a break point inside your BasicLabel7 Datachanged event and stepping thru it
-
Yes, I have installed EPPlus in Nuget packages.
I have created a file in Excel with the headers in it already and placed it in the Logs folder, I will try just the write data section to see if it populates the file, and removed the click events.
I do go online with the PLC to the PLCAddress value and control it, so far the code doesn't change it back or seem to act on it.
I tried the breakpoint before, but will try it in a stripped down to just the write command version.
Thanks for all the help.
-
Here is a version that you can test it out. Click the download arrow. Build solution. You'll need to change the PLC driver to match yours.
https://drive.google.com/file/d/1jL6am3Co1niucXWbljQn8ksQnRPvfvEy/view?usp=sharing
-
Bachphi,
Thanks.
Looking over your code it seems to be similar to mine. I will compare it line for line later.
This morning I am going to try skipping the create section with an already existing file with a set name, and use breakpoints if that doesn't write.
-
Today, with the Create section remarked out and breakpoints all over I repeated the Debug and every time it got to the same place and when I clicked Continue it jumped over a lot of commands and breakpoints and went right to End Using. I remarked out the If "True" and End If so this section would process.
The output ended with:
Exception thrown: 'System.NullReferenceException' in Microsoft.VisualBasic.dll
The program '[20836] AdvancedHMI.exe' has exited with code -1 (0xffffffff).
The rest of the output was Loaded [programs and files] and Skipped Loading Symbols - no other errors or warnings.
The spot it ran up to was the first line of data writing from the SerialDF1 driver - when I hit Continue there it kept jumping to the End Using line, I stopped and restarted Debug 5 times and replicated this each time. I marked the spot below with *'s. There were also breakpoints on the next few lines and at the start of the column.AutoFit section - but those were skipped over. And the HMI was talking to the PLC, displaying the correct data, and it did pass the Write "0" without faulting.
I tried moving the Write "0" to before this and it passed OK. The rowNumber did set to 3 at start and incremented to 4. I made sure "Book1.xlsx" was in the Debug, Debug\Logs and C:\Logs folders - just to see which one was changed (none were)
Private Sub BasicLabel4_ValueChanged(sender As Object, e As EventArgs) Handles BasicLabel4.ValueChanged
'If BasicLabel4.Value = "True" Then
Dim fileName As String = "Book1.xlsx"
Try
Using ExcelPackage As New ExcelPackage(New IO.FileInfo(".\Logs\" + fileName))
'Add one row and start add the data
rowNumber += 1
'***********************************************************************
'* Clear the Write-Data bit which is monitored with the BasicLabel4
'***********************************************************************
SerialDF1forSLCMicroCom1.Write(BasicLabel4.PLCAddressValue, "0")
********** Workbook.worksheet.Cells(rowNumber, 1).Value = SerialDF1forSLCMicroCom1.Read("F8:48")
Workbook.worksheet.Cells(rowNumber, 2).Value = SerialDF1forSLCMicroCom1.Read("F8:50")
Workbook.worksheet.Cells(rowNumber, 3).Value = SerialDF1forSLCMicroCom1.Read("F25:99")
Workbook.worksheet.Cells(rowNumber, 4).Value = SerialDF1forSLCMicroCom1.Read("F25:0")
Workbook.worksheet.Cells(rowNumber, 5).Value = SerialDF1forSLCMicroCom1.Read("N10:2")
Workbook.worksheet.Cells(rowNumber, 6).Value = SerialDF1forSLCMicroCom1.Read("N10:1")
Workbook.worksheet.Cells(rowNumber, 7).Value = SerialDF1forSLCMicroCom1.Read("F8:0")
Workbook.worksheet.Cells(rowNumber, 8).Value = SerialDF1forSLCMicroCom1.Read("F8:1")
Workbook.worksheet.Cells(rowNumber, 9).Value = SerialDF1forSLCMicroCom1.Read("F8:37")
Workbook.worksheet.Cells(rowNumber, 10).Value = SerialDF1forSLCMicroCom1.Read("N10:40")
Workbook.worksheet.Cells(rowNumber, 11).Value = SerialDF1forSLCMicroCom1.Read("N10:41")
Workbook.worksheet.Cells(rowNumber, 12).Value = SerialDF1forSLCMicroCom1.Read("N10:71")
Workbook.worksheet.Cells(rowNumber, 13).Value = SerialDF1forSLCMicroCom1.Read("N10:42")
Workbook.worksheet.Cells(rowNumber, 14).Value = SerialDF1forSLCMicroCom1.Read("N10:43")
Workbook.worksheet.Cells(rowNumber, 15).Value = SerialDF1forSLCMicroCom1.Read("N10:72")
Workbook.worksheet.Cells(rowNumber, 16).Value = SerialDF1forSLCMicroCom1.Read("N10:44")
Workbook.worksheet.Cells(rowNumber, 17).Value = SerialDF1forSLCMicroCom1.Read("N10:45")
Workbook.worksheet.Cells(rowNumber, 18).Value = SerialDF1forSLCMicroCom1.Read("N10:73")
Workbook.worksheet.Cells(rowNumber, 19).Value = SerialDF1forSLCMicroCom1.Read("N10:46")
Workbook.worksheet.Cells(rowNumber, 20).Value = SerialDF1forSLCMicroCom1.Read("N10:47")
Workbook.worksheet.Cells(rowNumber, 21).Value = SerialDF1forSLCMicroCom1.Read("N10:74")
Workbook.worksheet.Cells(rowNumber, 22).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()
End Using
Catch ex As Exception
Dim StatusLabel As String = "Status: Error = " & ex.Message
End Try
'End If
End Sub
Also, as per your example, I tried a Dim statement for each of the 22 values, then write the variables "Colmn##" but that didn't work either (I tried that before adding the Breakpoints) so I put it back as before.
-
Here is a screenshot showing the code with the breakpoints in the area
-
Aabeck, you dont need that many breakpoints, and you do not hit 'continue" . You 'should' STEP INTO to do thew step by step.
The fact that you had to comment out the first line ( 'If BasicLabel4.Value = "True" Then ) tells me that you did not toggle your bit to ON.
I would suggest you try out my solution, change the driver to see if its work, then start changing little by little and hopefully you can learn a few things from there
-
Bachphi,
I had to comment out the True bit, as I am using serial port comm's. When AHMI is using the COM port I can not be online with RSLinx to change the bit. It would be nice to have an Ethernet SLC5/05 here.
I started with more breakpoints than you see to see each command work, and deleted some working down to the trouble spot. I then added the breakpoints in the Workbook... section to see if it would go to another one, but it always jumped to End Use.
I will try your solution like you said and see how far I can get.
Thanks for helping so much.
-
If you are working with 5/03, what about DH485 port, the 1747-uic usb converter is not that expensive
-
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.
-
lol, No .. but I would ask why would u need 4 laptops? what about VM?
-
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.
-
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.
-
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.
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
-
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.