Processing signal and data in Advanced HMI

[Archie]

What are you using to write to Excel within VB?

[ENA]

My global variables for excel files:

Code: [Select]

Public Class GlobalVariables
    '**************************************************************************************************
    'Excel file1
    'data log
    Public Shared CellIndexRow_file1 As Long = 2 'prvi red je zaglavlje
    Public Shared oExcel_file1 As Object
    Public Shared oBook_file1 As Object
    Public Shared oSheet_file1 As Object
    '**************************************************************************************************

    '**************************************************************************************************
    'Excel file2
    'event list
    Public Shared CellIndexRow_file2 As Long = 2 'prvi red je zaglavlje
    Public Shared oExcel_file2 As Object
    Public Shared oBook_file2 As Object
    Public Shared oSheet_file2 As Object
    '**************************************************************************************************
End Class
In load event function of my main form (actually called "PregledSustava"), this is code to set all the variables:

Code: [Select]

Private Sub MainForm_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        '**************************************************************************************************
        'Excel file1 initialization, initialization of pointera on first empty row in Excel
        'data log
        GlobalVariables.oExcel_file1 = CreateObject("Excel.Application")
        GlobalVariables.oExcel_file1 = New Excel.Application
        GlobalVariables.oExcel_file1.Workbooks.Open("E:\Documents\Documents\AdvancedHMI\Proba_Excell\AdvancedHMI\bin\Debug\excel_logs\HMItest.xlsx")
        GlobalVariables.oBook_file1 = GlobalVariables.oExcel_file1.Workbooks(1)
        GlobalVariables.oSheet_file1 = GlobalVariables.oBook_file1.Worksheets(1)
        While (GlobalVariables.oSheet_file1.Range("A" + CStr(GlobalVariables.CellIndexRow_file1)).Value <> 0)
            GlobalVariables.CellIndexRow_file1 = GlobalVariables.CellIndexRow_file1 + 1
        End While
        '**************************************************************************************************

        '**************************************************************************************************
        'Excel file2 initialization, initialization of pointera on first empty row in Excel
        'event log
        GlobalVariables.oExcel_file2 = CreateObject("Excel.Application")
        GlobalVariables.oExcel_file2 = New Excel.Application
        GlobalVariables.oExcel_file2.Workbooks.Open("E:\Documents\Documents\AdvancedHMI\Proba_Excell\AdvancedHMI\bin\Debug\excel_logs\event_log.xlsx")
        GlobalVariables.oBook_file2 = GlobalVariables.oExcel_file2.Workbooks(1)
        GlobalVariables.oSheet_file2 = GlobalVariables.oBook_file2.Worksheets(1)
        While (GlobalVariables.oSheet_file2.Range("A" + CStr(GlobalVariables.CellIndexRow_file2)).Value <> 0)
            GlobalVariables.CellIndexRow_file2 = GlobalVariables.CellIndexRow_file2 + 1
        End While
        '**************************************************************************************************

        '**************************************************************************************************
        'start timer for periodical export of data in Excel
        DataPolling.Excel_export.Enabled = True
        'period of data export in seconds or minutes
        DataPolling.Excel_export.Interval = 5 * 1000 '*60
        '**************************************************************************************************

        '**************************************************************************************************
        'DataPolling form must be activated to start polling data, then hide it and show MainForm
        DataPolling.Show()
        DataPolling.Hide()
        Me.Show()
        '**************************************************************************************************
    End Sub
In DataPolling form is a timer to periodically export data, this is function that handles timer tick:

Code: [Select]

Private Sub Excel_export_tick(sender As Object, e As EventArgs) Handles Excel_export.Tick
        '**************************************************************************************************
        'writing data in Excel columns A-E, row determined by CellIndexRow_file1
        GlobalVariables.oSheet_file1.Range("A" + CStr(GlobalVariables.CellIndexRow_file1)).Value = GlobalVariables.CellIndexRow_file1 - 1
        GlobalVariables.oSheet_file1.Range("B" + CStr(GlobalVariables.CellIndexRow_file1)).Value = DateString
        GlobalVariables.oSheet_file1.Range("C" + CStr(GlobalVariables.CellIndexRow_file1)).Value = TimeOfDay()
        GlobalVariables.oSheet_file1.Range("D" + CStr(GlobalVariables.CellIndexRow_file1)).Value = DS_INT1.Value
        GlobalVariables.oSheet_file1.Range("E" + CStr(GlobalVariables.CellIndexRow_file1)).Value = DS_INT2.Value
        'go to next row
        GlobalVariables.CellIndexRow_file1 = GlobalVariables.CellIndexRow_file1 + 1
        'save changes to file
        GlobalVariables.oBook_file1.Save()
        '**************************************************************************************************
    End Sub
Function of data subscriber in DataPolling form for DataChanged, writes event in excel when PLC input changes. This is where I get double entries in file for every event:

Code: [Select]

Private Sub DS_NZS_DataChanged(sender As Object, e As Drivers.Common.PlcComEventArgs) Handles DS_NZS.DataChanged
'function that handles BasicIndicator color change
        Obradi_OpcaGreska()
        If DS_NZS.Value = True Then
            '**************************************************************************************************
            'write event in Excel event log
            GlobalVariables.oSheet_file2.Range("A" + CStr(GlobalVariables.CellIndexRow_file2)).Value = GlobalVariables.CellIndexRow_file2 - 1
            GlobalVariables.oSheet_file2.Range("B" + CStr(GlobalVariables.CellIndexRow_file2)).Value = DateString
            GlobalVariables.oSheet_file2.Range("C" + CStr(GlobalVariables.CellIndexRow_file2)).Value = TimeOfDay()
            GlobalVariables.oSheet_file2.Range("D" + CStr(GlobalVariables.CellIndexRow_file2)).Value = "ALARM Low level"
            GlobalVariables.oSheet_file2.Range("E" + CStr(GlobalVariables.CellIndexRow_file2)).Value = "Pumping station 1"
            GlobalVariables.oSheet_file2.Range("F" + CStr(GlobalVariables.CellIndexRow_file2)).Value = "General"
            'go to next row in file
            GlobalVariables.CellIndexRow_file2 = GlobalVariables.CellIndexRow_file2 + 1
            'save changes to file
            GlobalVariables.oBook_file2.Save()
            '**************************************************************************************************
        End If
    End Sub
When closing my main form:

Code: [Select]

Protected Overrides Sub OnFormClosing(e As System.Windows.Forms.FormClosingEventArgs)
        DataPolling.Excel_export.Enabled = False
        GlobalVariables.oExcel_file1.Quit()
        GlobalVariables.oExcel_file2.Quit()
        Environment.Exit(0)
        MyBase.OnFormClosing(e)
    End Sub

[Archie]

Try putting this in your main form:

Code: [Select]

    Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        AddHandler AppDomain.CurrentDomain.UnhandledException, AddressOf MyApplication_UnhandledException
    End Sub

    Private Sub MyApplication_UnhandledException(ByVal sender As Object, ByVal e As System.UnhandledExceptionEventArgs)
        '* Insert code to close open files
        MsgBox("Unhandled Exception occurred")
    End Sub


[ENA]

Works great! I have no open files left after application crash.