AdvancedHMI Software
General Category => Open Discussion => Topic started by: bob1371 on September 12, 2017, 08:00:12 PM
-
Hello,
I've been reading through the forum and particularly looking at the KeepingRecordsFromCLXtoExcelFile demo. I'm looking to copy an array of the last 500 cycle time averages for each of the 7 sections of the cell. A total of 3500 tags to 1 excel file.
I have 0 coding experience but do have a few AHMI projects up and working. I've downloaded the KeepingRecordsFromCLXtoExcelFile AHMI project and have set BasicLabel1 to Array address. Currently when I start the project I get the value from AverageCycleArray[0] displayed. I want to work with getting this to excel then move on to the complete array.
I believe I read in one of the post that I can save this to an excel file even without MS Office installed????
If so how to do this?
Will I need a BasicLabel for each array element?
Thanks,
-
What do you want to trigger the transfer? A bit in the PLC, a click of a button, etc.?
Once that is determined, you would use a few lines of code to read the PLC data, then write it to the PLC.
-
Archie,
I would use a bit from PLC to do this.
Thanks.
-
- Add a Datasubscriber to your form
- Set PLCAddressValue to the trigger bit
- Double click the DataSubscriber to get back to the code
- Start with this code:
if e.ErrorCode=0 AndAlso e.Values.Count>0 and Also e.Value(0)="True" then
Dim MyValues() as string=EthernetIPforCLXCom1.Read(AverageCycleArray[0] , 500)
'* Transfer the values to Excel
End If
In order to write to an Excel file, you will need to use the EPPlus library.
- Project->Manage Nuget Packages
- Select Browse
- In the Seach Bar enter EPPLUS
- Select the top item, then click the Install button
From here I will need to look at the Excel sample project because I do not know the code right off the top of my head.
-
Archie,
I did get some time to look through this.
I did everything you suggested in last post.
When I tried to get EPPLUS it showed I already had it installed.
I then went on to reading this post.
http://www.advancedhmi.com//forum/index.php?topic=679.0
and tried to add Microsoft Excel Object Library but it could not be found.
That's where I'm stuck at this time.
Here is my Mainform.VB code.
Thanks.
Public Class MainForm
'*******************************************************************************
'* Stop polling when the form is not visible in order to reduce communications
'* Copy this section of code to every new form created
'*******************************************************************************
Private NotFirstShow As Boolean
Private ReadOnly VCell_1A_FES_Cycle_Average As String
Private ReadOnly oSheet As Object
Private Sub Form_VisibleChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.VisibleChanged
''* Do not start comms on first show in case it was set to disable in design mode
'If NotFirstShow Then
' AdvancedHMIDrivers.Utilities.StopComsOnHidden(components, Me)
'Else
' NotFirstShow = True
'End If
End Sub
'***********************************************************
'* When the BasicLabel gets a new value, this event fires
'***********************************************************
Private Sub BasicLabel1_ValueChanged(sender As Object, e As EventArgs) Handles BasicLabel1.ValueChanged
If BasicLabel1.Value = "True" Then
Try
'***************************************
'* Open the existing Excel file
'***************************************
Using ExcelPackage As New OfficeOpenXml.ExcelPackage(New System.IO.FileInfo(".\HistoricalData.xlsx"))
'***************************************
'* Search for the first blank Excel row
'***************************************
Dim FirstBlankRow As Integer = 1
While ExcelPackage.Workbook.Worksheets(1).Cells(FirstBlankRow, 1).Value IsNot Nothing
FirstBlankRow += 1
End While
'**************************************
'* Show om the form the row to use
'**************************************
StatusLabel.Text = "Status: Blank Row found at " & FirstBlankRow
'***********************************************************************
'* Read the first tag within the UDT, then store in the Excel Worksheet
'***********************************************************************
Dim StartTime As String = EthernetIPforCLXCom1.Read("FillRecords[0].StartTime")
ExcelPackage.Workbook.Worksheets(1).Cells(FirstBlankRow, 1).Value = StartTime
'***********************************************************************
'* Read the second tag within the UDT, then store in the Excel Worksheet
'***********************************************************************
Dim EndTime As String = EthernetIPforCLXCom1.Read("FillRecords[0].EndTime")
ExcelPackage.Workbook.Worksheets(1).Cells(FirstBlankRow, 1).Value = EndTime
'**********************************
'* Save the Excel file changes
'**********************************
StatusLabel.Text = "Status: Saving Excel File on " & Now
ExcelPackage.Save()
'***********************************************************************
'* Clear the TruckCompleted bit which is monitored with the BasicLabel
'***********************************************************************
EthernetIPforCLXCom1.Write(BasicLabel1.PLCAddressValue, "0")
End Using
Catch ex As Exception
StatusLabel.Text = "Status: Error = " & ex.Message
End Try
End If
End Sub
Private Sub Label2_Click(sender As Object, e As EventArgs) Handles Label2.Click
End Sub
Private Sub MainForm_Load(sender As Object, e As EventArgs) Handles MyBase.Load
End Sub
Private Sub DataSubscriber1_DataChanged(sender As Object, e As Drivers.Common.PlcComEventArgs)
If e.ErrorMessage = 0 AndAlso e.Values.Count > 0 AndAlso e.Values(0) = "true" Then
Dim MyValues() As String = EthernetIPforCLXCom1.Read(VCell_1A_FES_Cycle_Average(0), 500)
oSheet.Cells(1, 1).Value = e.Values(0)
'* Transfer the values to Excel
End If
End Sub
Private Sub EthernetIPforCLXCom1_DataReceived(sender As Object, e As Drivers.Common.PlcComEventArgs) Handles EthernetIPforCLXCom1.DataReceived
End Sub
Private Sub BasicLabel1_Click(sender As Object, e As EventArgs) Handles BasicLabel1.Click
End Sub
Private Sub DataSubscriber1_DataChanged_1(sender As Object, e As Drivers.Common.PlcComEventArgs) Handles DataSubscriber1.DataChanged
End Sub
Private Sub QuickStartLabel_Click(sender As Object, e As EventArgs) Handles QuickStartLabel.Click
End Sub
End Class
-
Start with a blank AdvancedHMI solution then follow the steps above. To continue with my code sample:
if e.ErrorCode=0 AndAlso e.Values.Count>0 and Also e.Value(0)="True" then
Dim MyValues() as string=EthernetIPforCLXCom1.Read(AverageCycleArray[0] , 500)
'* Transfer the values to Excel
Using ExcelPackage As New OfficeOpenXml.ExcelPackage(New System.IO.FileInfo(".\HistoricalData.xlsx"))
For I=0 to MyValues.Count-1
ExcelPackage.Workbook.Worksheets(1).Cells(1, 1).Value = MyValues(I)
Next
End Using
End If
You do not want the Microsoft Excel Object Library. That is only used if you have Excel installed and want to control it from AdvancedHMI.
-
Thanks Archie.
I think I have everything but it doesn't like the - When calling out the array. Gives the following message
1>C:\Users\Owner\Desktop\AHMI Data Collection\AdvancedHMI\MainForm.vb(37,92): error BC30203: Identifier expected.
1>C:\Users\Owner\Desktop\AHMI Data Collection\AdvancedHMI\MainForm.vb(37,92): error BC32017: Comma, ')', or a valid expression
-
Can you post your line 37 of your code, the place where the error occurs.
-
Dim MyValues() As String = EthernetIPforCLXCom1.Read(VCell_1A_FES_Cycle_Average[0], 500)
If I take the [ 0] out (Dim MyValues() As String = EthernetIPforCLXCom1.Read(VCell_1A_FES_Cycle_Average, 500)
errors are gone and it lets me run the script. Not sure if it's working though as I don't know where the file will be stored.
Thanks.
-
Image attached
-
The tag name needs to be in quotes:
Dim MyValues() As String = EthernetIPforCLXCom1.Read("VCell_1A_FES_Cycle_Average[0]", 500)
-
Thanks Archie,
Looks like it may do the trick. I will set up and trigger my DataScriber every 10 minutes and see how it works.
-
Archie,
Doesn't look like I'm writing to an Excel file.
Will get back at it tomorrow afternoon.
-
Archie,
Still haven't been able to get this working. I try a few things and it doesn't work. Delete and start over with a fresh AHMI and repeat.
Here are the steps i'm going through.
Fresh instance of AHMI
Open mainform -> Build Solution
Drag EthernetIPforCLXCom1 to form -> Set IP Address
Drag DataScribber to form -> Set PLC tag in PLCAddressValue
Double Click DataScribber -> Paste code that you supplied in post reply #5
Project -> Manage NuGet Packages -> Install EPPlus V4.1.1
I then double click dataScribber again to get to code. I will go line by line and list errors.
Line 33 If e.ErrorCode = 0 AndAlso e.Values.Count > 0 And Also e.Value(0) = "True" Then
1. ErrorCode is not a memeber of PLCComEvent.Args. I can change to ErrorMessage or ErrorID and it's happy.
2. The 2nd And Also has a space. I assume it should be without space as the first one is.
3. If I remove space in And Also then e.Value "Value is not a memeber of PLCComEvent.Args" but I can change to e.Values and it's ok.
Line 39 For I = 0 To MyValues.Count - 1
1. Count is not a member of string ()
I make changes and this is the final code. I can publish and run the executable. Set my tag to trigger in PLC but still nothing. I tried added C: to this .\HistoricalData.xlsx (C:\HistoricalData.xlsx) thinking this will be the location and file name but still nothing.
Imports System.Linq
Public Class MainForm
'*******************************************************************************
'* Stop polling when the form is not visible in order to reduce communications
'* Copy this section of code to every new form created
'*******************************************************************************
Private NotFirstShow As Boolean
Private Sub Form_VisibleChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.VisibleChanged
'* Do not start comms on first show in case it was set to disable in design mode
If NotFirstShow Then
AdvancedHMIDrivers.Utilities.StopComsOnHidden(components, Me)
Else
NotFirstShow = True
End If
End Sub
'***************************************************************
'* .NET does not close hidden forms, so do it here
'* to make sure forms are disposed and drivers close
'***************************************************************
Private Sub MainForm_FormClosing(sender As Object, e As FormClosingEventArgs) Handles Me.FormClosing
Dim index As Integer
While index < My.Application.OpenForms.Count
If My.Application.OpenForms(index) IsNot Me Then
My.Application.OpenForms(index).Close()
End If
index += 1
End While
End Sub
Private Sub DataSubscriber1_DataChanged(sender As Object, e As Drivers.Common.PlcComEventArgs) Handles DataSubscriber1.DataChanged
If e.ErrorMessage = 0 AndAlso e.Values.Count > 0 AndAlso e.Values(0) = "True" Then
Dim MyValues() As String = EthernetIPforCLXCom1.Read("VCell1A_Actual_Cycle_Time_Real[0]", 500)
'* Transfer the values to Excel
Using ExcelPackage As New OfficeOpenXml.ExcelPackage(New System.IO.FileInfo(".\HistoricalData.xlsx"))
For I = 0 To MyValues.Count - 1
ExcelPackage.Workbook.Worksheets(1).Cells(1, 1).Value = MyValues(I)
Next
End Using
End If
End Sub
End Class
-
It should be e.ErrorID
Count should be Length
If e.ErrorID = 0 AndAlso e.Values.Count > 0 AndAlso e.Values(0) = "True" Then
Console.WriteLine("About to read the data")
Dim MyValues() As String = EthernetIPforCLXCom1.Read("VCell1A_Actual_Cycle_Time_Real[0]", 500)
Console.WriteLine(MyValues.Length & " elements read.")
'* Transfer the values to Excel
Using ExcelPackage As New OfficeOpenXml.ExcelPackage(New System.IO.FileInfo(".\HistoricalData.xlsx"))
For I = 0 To MyValues.Length - 1
Console.WriteLine("Element " & I & "=" & MyValues(I))
ExcelPackage.Workbook.Worksheets(1).Cells(1, 1).Value = MyValues(I)
Next
End Using
End If
-
Great thanks. I will give it a try this afternoon.
I've started an online basic learning VB course. Hopefully be able to figure some of this stuff out soon.
-
Here is another resource for useful tutorials:
http://advancedhmi.com/forum/index.php?topic=298.msg1056#msg1056
-
Archie,
Thanks for the additional tutorial resources....
Been a busy week so didn't get to mess with this much. Only a few lines running tonight so thought I would try again.
As for this issue, I still cannot get data. I have everything as you have shown. I have the EPPLUS installed via Manage NuGet Packages.
I have my datascribber set up with a tag. I can trigger that tag with the application running and still get nothing. Looking at the VB Code during debug I get no errors.
I've started over several times trying a couple different things to no avail
Not sure whats missing.
-
After you added the lines of code ("Console.WriteLine..."), do you see anything in your Output Window when you run the application?
Add a BasicLabel to the form and set PLCAddressValue to the same tag name you put in the DataSubscriber.
-
Ok I had not checked the output window previously. I start the application in debug. Toggle my tag for Datasubscriber the close debug.
Here is what I get.
About to read the data
500Elements read.
Element 0=87.945
Exception Thrown: 'System.IndexOutOfRangeException' in EPPlus.dll
The thread 0x22b4 has exited with code 0 (0x0).
The thread 0x214c has exited with code 0 (0x0).
The thread 0x2154 has exited with code 0 (0x0).
The program '[10124] AdvancedHMI.exe' has exited with code 0 (0x0)
So it is actually reading but having trouble writing the Excel file? Do I need to register EPPlus.dll file?
Thanks.
-
Exception Thrown: 'System.IndexOutOfRangeException' in EPPlus.dll
This typically happens when referencing a cell or sheet 0 with EPPlus
I did happen to notice a mistake in my code. It should be this:
ExcelPackage.Workbook.Worksheets(1).Cells(1, I + 1).Value = MyValues(I)
-
Based on Archie's comments and looking at the code, it appears that you have probably used the letter "I" instead of the number "one" somewhere. Its unfortunate that these look so much alike. Here is another version of the line with verbal comments that may help you.
ExcelPackage.Workbook.Worksheets(number one).Cells(number one, Letter "I" + number one).Value = MyValues(Letter "I")
-
Thanks Archie and Doug.
I will look everything over again when I get into work tonight.
-
To help ensure there is no 1 and I confusion, change the variable name to something more meaningful. And also break it down more to help narrow the problem.
For Index = 0 To MyValues.Length - 1
Console.WriteLine("Element " & Index & "=" & MyValues(Index))
Dim ws As OfficeOpenXml.ExcelWorksheet = ExcelPackage.Workbook.Worksheets(1)
Console.WriteLine("Worksheet OK")
ws.Cells(1, Index+1).Value = MyValues(Index)
Next
-
I think I'm finally making some progress. :)
I commented out the loop and tried to write just a static value to excel sheet but got the same alarm. I then just let the data output to console and when I looked at the output tab sure enough it's all there 0 - 499.
I put things back then copied the last code Archie posted. I do believe I have it now, with the exception of where is the file located? On the output tab I now have the following.
About to read the data
500elements read.
Element 0=32.88
Worksheet OK
Element 1=195.33
Worksheet OK
Element 2=27.885
Worksheet OK
Element 3=51.63
Worksheet OK
All the way down to Element 499.
Using ExcelPackage As New OfficeOpenXml.ExcelPackage(New IO.FileInfo("C:\Data.xlsx"))In this line I believe I'm telling it to create file "Data.xlsx in C:\ " but I have no files????
I will keep plugging away. Thanks for all the help so far.
-
+++++++SUCCESS+++++++
Added ExcelPackage.SaveAs () right outside the loop and it's now creating the Excel file and placing data in column A rows 1-500.
If e.ErrorId = 0 AndAlso e.Values.Count > 0 AndAlso e.Values(0) = "True" Then
Console.WriteLine("About to read the data")
Dim MyValues() As String = EthernetIPforCLXCom1.Read("VCell_1A_FES_Cycle_Average[0]", 500)
Console.WriteLine(MyValues.Length & "elements read.")
'* Transfer the values to Excel
Using ExcelPackage As New OfficeOpenXml.ExcelPackage(New IO.FileInfo("C:\Data.xlsx"))
ExcelPackage.Workbook.Worksheets.Add("test")
For Index = 0 To MyValues.Length - 1
Console.WriteLine("Element " & Index & "=" & MyValues(Index))
Dim ws As OfficeOpenXml.ExcelWorksheet = ExcelPackage.Workbook.Worksheets(1)
Console.WriteLine("Worksheet OK")
Dim CellNum As String = "A" & (Index + 1)
ws.Cells(CellNum).Value = MyValues(Index)
Next
ExcelPackage.Save()
End Using
End If
I will play around with adding headers to the cells and formatting how I want.
Thanks again....
-
I've added 2 more DataSubscribers and now collecting for all 3 cell. I have it set up to label each of the 3 tabs and populate Column A with the data. Everything Good so far.
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("About to read the data")
Dim MyValues() As String = EthernetIPforCLXCom1.Read("VCell_1A_FES_Cycle_Average[0]", 500)
Console.WriteLine(MyValues.Length & "elements rea8d.")
'*Transfer the values to Excel
Using ExcelPackage As New OfficeOpenXml.ExcelPackage(New IO.FileInfo("C:\Data.xlsx"))
ExcelPackage.Workbook.Worksheets.Add("VCell 1A")
For Index = 0 To MyValues.Length - 1
Console.WriteLine("Element " & Index & "=" & MyValues(Index))
Dim ws As OfficeOpenXml.ExcelWorksheet = ExcelPackage.Workbook.Worksheets(1)
Console.WriteLine("Worksheet OK")
Dim CellNum As String = "A" & (Index + 1)
ws.Cells(CellNum).Value = MyValues(Index)
Next
ExcelPackage.Save()
End Using
End If
End Sub
Private Sub DataSubscriber2_DataChanged(sender As Object, e As Drivers.Common.PlcComEventArgs) Handles DataSubscriber2.DataChanged
If e.ErrorId = 0 AndAlso e.Values.Count > 0 AndAlso e.Values(0) = "True" Then
Console.WriteLine("About to read the data")
Dim MyValues() As String = EthernetIPforCLXCom1.Read("Inspect_FES_Cycle_Average[0]", 500)
Console.WriteLine(MyValues.Length & "elements rea8d.")
'*Transfer the values to Excel
Using ExcelPackage As New OfficeOpenXml.ExcelPackage(New IO.FileInfo("C:\Data.xlsx"))
ExcelPackage.Workbook.Worksheets.Add("Inspect")
For Index = 0 To MyValues.Length - 1
Console.WriteLine("Element " & Index & "=" & MyValues(Index))
Dim ws As OfficeOpenXml.ExcelWorksheet = ExcelPackage.Workbook.Worksheets(2)
Console.WriteLine("Worksheet OK")
Dim CellNum As String = "A" & (Index + 1)
ws.Cells(CellNum).Value = MyValues(Index)
Next
ExcelPackage.Save()
End Using
End If
End Sub
Private Sub DataSubscriber3_DataChanged(sender As Object, e As Drivers.Common.PlcComEventArgs) Handles DataSubscriber3.DataChanged
If e.ErrorId = 0 AndAlso e.Values.Count > 0 AndAlso e.Values(0) = "True" Then
Console.WriteLine("About to read the data")
Dim MyValues() As String = EthernetIPforCLXCom1.Read("Repair_FES_Cycle_Average[0]", 500)
Console.WriteLine(MyValues.Length & "elements rea8d.")
'*Transfer the values to Excel
Using ExcelPackage As New OfficeOpenXml.ExcelPackage(New IO.FileInfo("C:\Data.xlsx"))
ExcelPackage.Workbook.Worksheets.Add("Repair")
For Index = 0 To MyValues.Length - 1
Console.WriteLine("Element " & Index & "=" & MyValues(Index))
Dim ws As OfficeOpenXml.ExcelWorksheet = ExcelPackage.Workbook.Worksheets(3)
Console.WriteLine("Worksheet OK")
Dim CellNum As String = "A" & (Index + 1)
ws.Cells(CellNum).Value = MyValues(Index)
Next
ExcelPackage.Save()
End Using
End If
End Sub
-
I'm Back. LOL
I've modified the code to collect data from all 8 cells with the goal to create the .xlsx file first of the month then write the data to a different tab each day. I'm writing the date as tab name (yyyy mm dd) and headers for the columns I'm using. However when the date rolls over it creates the new tab with the correct date but doesn't write the data to it. I've tried various ways to mess around with this "ExcelPackage.Workbook.Worksheets(1)" but no luck. Can you point me in the right direction???
Also is it possible to get a confirmation bit back to PLC that data was written??
The code and .xlsx file attached.
Thanks.
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("About to read the data")
Dim MyValues0() As String = EthernetIPforCLXCom1.Read("VCell1B_FES_Cycle_Average[0]", 500)
Dim MyValues1() As String = EthernetIPforCLXCom1.Read("VCell_1A_FES_Cycle_Average[0]", 500)
Dim MyValues2() As String = EthernetIPforCLXCom1.Read("VCell_2B_FES_Cycle_Average[0]", 500)
Dim MyValues3() As String = EthernetIPforCLXCom1.Read("VCell_2A_FES_Cycle_Average[0]", 500)
Dim MyValues4() As String = EthernetIPforCLXCom1.Read("VCell_3B_FES_Cycle_Average[0]", 500)
Dim MyValues5() As String = EthernetIPforCLXCom1.Read("VCell_3A_FES_Cycle_Average[0]", 500)
Dim MyValues6() As String = EthernetIPforCLXCom1.Read("Inspect_FES_Cycle_Average[0]", 500)
Dim MyValues7() As String = EthernetIPforCLXCom1.Read("Repair_FES_Cycle_Average[0]", 500)
Dim MyValues8 As String = EthernetIPforCLXCom1.Read("MDY5")
Console.WriteLine(MyValues0.Length & "elements read VCell_1B.")
Console.WriteLine(MyValues1.Length & "elements read VCell_1A.")
Console.WriteLine(MyValues2.Length & "elements read VCell_2B.")
Console.WriteLine(MyValues3.Length & "elements read VCell_2A.")
Console.WriteLine(MyValues4.Length & "elements read VCell_3B.")
Console.WriteLine(MyValues5.Length & "elements read VCell_3A.")
Console.WriteLine(MyValues6.Length & "elements read Inspect.")
Console.WriteLine(MyValues7.Length & "elements read Repair.")
Console.WriteLine("All Data Read")
'*Transfer the values to Excel
'*Creates Excel file
Using ExcelPackage As New OfficeOpenXml.ExcelPackage(New IO.FileInfo("C:\Data.xlsx"))
ExcelPackage.Workbook.Worksheets.Add(MyValues8)
Console.WriteLine("ws creation ok")
'*Writes the Headers in Row 1
ExcelPackage.Workbook.Worksheets(1).Cells("A1").Value = "VCell 1B"
ExcelPackage.Workbook.Worksheets(1).Cells("C1").Value = "VCell 1A"
ExcelPackage.Workbook.Worksheets(1).Cells("E1").Value = "VCell 2B"
ExcelPackage.Workbook.Worksheets(1).Cells("G1").Value = "VCell 2A"
ExcelPackage.Workbook.Worksheets(1).Cells("I1").Value = "VCell 3B"
ExcelPackage.Workbook.Worksheets(1).Cells("K1").Value = "VCell 3A"
ExcelPackage.Workbook.Worksheets(1).Cells("M1").Value = "Inspect"
ExcelPackage.Workbook.Worksheets(1).Cells("O1").Value = "Repair"
Console.WriteLine("header creation ok")
'*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(1)
Dim CellNum As String = "A" & (Index + 3)
ws.Cells(CellNum).Value = MyValues0(Index)
Next
Console.WriteLine("Worksheet OK 1B")
For Index = 0 To MyValues1.Length - 1
Console.WriteLine("Element " & Index & "=" & MyValues1(Index))
Dim ws As OfficeOpenXml.ExcelWorksheet = ExcelPackage.Workbook.Worksheets(1)
Dim CellNum As String = "C" & (Index + 3)
ws.Cells(CellNum).Value = MyValues1(Index)
Next
Console.WriteLine("Worksheet OK 1A")
For Index = 0 To MyValues2.Length - 1
Console.WriteLine("Element " & Index & "=" & MyValues2(Index))
Dim ws As OfficeOpenXml.ExcelWorksheet = ExcelPackage.Workbook.Worksheets(1)
Dim CellNum As String = "E" & (Index + 3)
ws.Cells(CellNum).Value = MyValues2(Index)
Next
Console.WriteLine("Worksheet OK 2B")
For Index = 0 To MyValues3.Length - 1
Console.WriteLine("Element " & Index & "=" & MyValues3(Index))
Dim ws As OfficeOpenXml.ExcelWorksheet = ExcelPackage.Workbook.Worksheets(1)
Dim CellNum As String = "G" & (Index + 3)
ws.Cells(CellNum).Value = MyValues3(Index)
Next
Console.WriteLine("Worksheet OK 2A")
For Index = 0 To MyValues4.Length - 1
Console.WriteLine("Element " & Index & "=" & MyValues4(Index))
Dim ws As OfficeOpenXml.ExcelWorksheet = ExcelPackage.Workbook.Worksheets(1)
Dim CellNum As String = "I" & (Index + 3)
ws.Cells(CellNum).Value = MyValues4(Index)
Next
Console.WriteLine("Worksheet OK 3B")
For Index = 0 To MyValues5.Length - 1
Console.WriteLine("Element " & Index & "=" & MyValues5(Index))
Dim ws As OfficeOpenXml.ExcelWorksheet = ExcelPackage.Workbook.Worksheets(1)
Dim CellNum As String = "K" & (Index + 3)
ws.Cells(CellNum).Value = MyValues5(Index)
Next
Console.WriteLine("Worksheet OK 3A")
For Index = 0 To MyValues6.Length - 1
Console.WriteLine("Element " & Index & "=" & MyValues6(Index))
Dim ws As OfficeOpenXml.ExcelWorksheet = ExcelPackage.Workbook.Worksheets(1)
Dim CellNum As String = "M" & (Index + 3)
ws.Cells(CellNum).Value = MyValues6(Index)
Next
Console.WriteLine("Worksheet OK Inspect")
For Index = 0 To MyValues7.Length - 1
Console.WriteLine("Element " & Index & "=" & MyValues7(Index))
Dim ws As OfficeOpenXml.ExcelWorksheet = ExcelPackage.Workbook.Worksheets(1)
Dim CellNum As String = "O" & (Index + 3)
ws.Cells(CellNum).Value = MyValues7(Index)
Next
Console.WriteLine("Worksheet OK Repair")
'*Saves.xlsl file
ExcelPackage.Save()
End Using
End If
End Sub
-
Figured out this last issue. I was using Concatenate to build yyyy/mm/dd I changed it to yyyy.mm.dd and working as it should.
-
Archie,
For the projects I am working on I am using Panasonic Tough book with Win 7.
After creating I am loading to Mini PC which is running Win 10. Everything was working fine but when I got the data collection stuff worked out I added the datasubscriber and copied my code. Everything works but doesn't create the file. I've tried on 2 different computers with Win 10 and same thing. I can run the same application on Win 7 and all is fine.
Any info on why this wouldn't work on Win10?
Thanks.
-
Where are you storing the file? Windows 10 may be more restrictive about writing to certain directories. If you are writing to the same directory as the executable by using ".\MyFile.xlsx", then it should work fine.
-
I'm running the application off USB drive but storing file to C:\Data.
Changed it to write file to USB drive and working fine.
Thanks.
-
Hi Bob
I was wondering if you dabbled with the cell formatting yet? More towards converting the data type from text to number.
-
I just had it Autosize the width to fit the headers. As for converting to a number, the team that will be gathering this data for trending will just multiply by 1 when they do the import.
You might look at this and it may lead you in right direction.
worksheet.cells["C2:C5"].style.Numberformat.format = "#,###"
-
Thanks!