CreateElektroDatabase

Sub xo_CustomConfig()
    Const xlCellTypeLastCell = 11
    Const xlValues = -4163

    Set objExcel = CreateObject("Excel.Application")
    Set objWorkbook = objExcel.Workbooks.Open( "c:\temp\TB0806-BD-001.xlsx" )
    objExcel.Visible = True

    Set list = CreateObject("System.Collections.ArrayList")

    Dim i
    Dim enabled
    Dim lastRow
    Dim sheet
    Dim offset

    For Each sheet In objExcel.ActiveWorkbook.Sheets
        lastRow = sheet.UsedRange.SpecialCells(xlCellTypeLastCell).row
        Set r = sheet.UsedRange.Find("Incluído")
        offset = 0
        If Not r Is Nothing Then
            For i = 2 To lastRow
                ' TODO: desabilitar quando X estiver taxado.

                enabled = sheet.Cells(i, r.Column).Value = "X" Or _
                        sheet.Cells(i, r.Column).Value = "x"

                If enabled Then
                    a = sheet.Cells(i, "F").Value   ' Name
                    b = sheet.Cells(i, "G").Value   ' DocString
                    c = GetTagKind( sheet.Name )
                    d = sheet.Cells(i, sheet.UsedRange.Find("IEC-61850").Column).Value  ' IEC61850

                    Set m_tag = New Tag.Init(a, b, c, d)

                    IEC61850 m_tag
                    DNPMaster m_tag, offset
                    DNPSlave m_tag, offset

                    offset = offset + 1
                    ' list.Add m_tag
                End If
            Next
        End If
    Next
    objExcel.Application.Quit

    MsgBox "Configuracao concluida"
End Sub

'*********************************** class ************************************'
Class Tag
    Public AdviseType,AllowRead,AllowWrite,EnableDriverEvent
    Public DocString,Name
    Public N1,N2,N3,N4,ParamDevice,ParamItem
    Public Kind, Params
    Public Folder

    Private arr

    Public Function Init(m_Name, m_DocString, m_Kind, m_Params)
        Set Init = Me
        Name = m_Name
        DocString = m_DocString
        Kind = m_Kind
        Params = m_Params
        Folder = GetFolder( m_Kind )
        GetParams()

        Select Case m_Kind
            Case "DD"   : AllowRead = True : AllowWrite = False : N1 = 1 : N2 = 1 : N3 = 402
            Case "DS"   : AllowRead = True : AllowWrite = False : N1 = 1 : N2 = 1 : N3 = 202
            Case "MA"   : AllowRead = True : AllowWrite = False : N1 = 1 : N2 = 1 : N3 = 3205
            Case "CD"   : AllowRead = False: AllowWrite = True  : N1 = 1 : N2 = 5 : N3 = 1201
            Case "SP"   : AllowRead = False: AllowWrite = True  : N1 = 1 : N2 = 5 : N3 = 4103
        End Select
    End Function

    Private Function GetFolder( kind )
        Select Case kind
            Case "DD", "DS" : GetFolder = "Digitais"
            Case "MA"       : GetFolder = "Analogicos"
            Case "CD"       : GetFolder = "Comandos"
            Case "SP"       : GetFolder = "Setpoints"
        End Select
    End Function

    Private Sub GetParams()
        arr = Split(Replace(Params & ".#.#", "/", "."), ".") '
        Select Case m_Kind
            Case "MA"
                If UBound(arr) = 7 Then
                    If (arr(3) = "OpCnt") Then
                        ParamItem = arr(2) & "$ST$OpCnt$stVal"
                    Else
                        ParamItem = arr(2) & "$" & arr(5) & "$" & arr(3) & "$" & arr(4) & "$cVal$mag$f"
                    End If
                ElseIf UBound(arr) = 6 Then
                    ParamItem = arr(2) & "$" & arr(4) & "$" & arr(3) & "$mag$f"
                End If
            Case Else
                If arr(4) = "#" Then
                    arr(4) = "ST"
                ElseIf arr(5) = "#" Then
                    arr(5) = GetAttribute(arr(3))
                End If
                ParamItem = arr(2) & "$" & arr(4) & "$" & arr(3) & "$" & arr(5)
        End Select

        ParamDevice = arr(0) & ":" & arr(0) & arr(1)
    End Sub




    Private Function GetAttribute(ByVal str)
        Dim regex
        Set regex = CreateObject("VBScript.RegExp")

        With regex
            .Global = True
            .MultiLine = False
            .IgnoreCase = False
            .Pattern = "^(Op|TrDev.*[0-9]|Tr|OpCls|RecOK|RestST|RecCyc.*[0-9]|DefTrip)$"
        End With

        GetAttribute = IIf(regex.test(str), "general", "stVal")
    End Function
End Class
'*********************************** /class ***********************************'

Function GetTagKind( name )
    ' TODO:
    Select Case True
        Case InStr(name, "Duplos") > 0  : GetTagKind = "DD"
        Case InStr(name, "Simples") > 0 : GetTagKind = "DS"
        Case InStr(name, "Ana") > 0     : GetTagKind = "MA"
        Case InStr(name, "Comand") > 0  : GetTagKind = "CD"
        Case InStr(name, "metros") > 0  : GetTagKind = "SP"
        Case Else                       : GetTagKind = ""
    End Select
End Function
Sub IEC61850( tag )
    Set driver = Application.GetObject("Aquisicao.[61850].IEC61850")

    On Error Resume Next
    Set folder = driver.Item( tag.Folder )
    If Err Then Set folder = driver.AddObject( "IOFolder", True, tag.Folder )
    On Error Goto 0

    Set bind = folder.AddObject("IOTag", False)
    With bind
        ' .AdviseType = 0
        .AllowRead = tag.AllowRead
        .AllowWrite = tag.AllowWrite
        ' .EnableDeadBand = tag.EnableDeadBand
        ' .EnableDriverEvent = tag.EnableDriverEvent
        .DocString = tag.DocString
        .Name = tag.Name
        .ParamDevice = tag.ParamDevice
        .ParamItem = tag.ParamItem
    End With

End Sub
Sub DNPMaster( tag , offset)
    Set driver = Application.GetObject(Parent.Parent.Name & "_IO_01")

    On Error Resume Next
    Set folder = driver.Item( tag.Folder )
    If Err Then Set folder = driver.AddObject( "IOFolder", True, tag.Folder )
    On Error Goto 0

    Set bind = folder.AddObject("IOTag", False)
    With bind
        ' .AdviseType = 0
        .AllowRead = tag.AllowRead
        .AllowWrite = tag.AllowWrite
        ' .EnableDeadBand = tag.EnableDeadBand
        ' .EnableDriverEvent = tag.EnableDriverEvent
        .DocString = tag.DocString
        .Name = tag.Name
        .N1 = tag.N1
        .N2 = tag.N2
        .N3 = tag.N3
        .N4 = offset
    End With
End Sub
Sub DNPSlave( tag , offset)
    Set driver = Application.GetObject("Distribuicao.DNP3Slave")

    On Error Resume Next
    Set folder = driver.Item( tag.Folder )
    If Err Then Set folder = driver.AddObject( "IOFolder", True, tag.Folder )
    On Error Goto 0


    ' TODO
    Select Case tag.Kind
        Case "DS", "DD" : tag.N1 = 21
        Case Else       : tag.N1 = 0
    End Select

    '
    Set bind = folder.AddObject("IOTag", False)
    With bind
        ' .AdviseType = 0
        .AllowRead = Not tag.AllowRead
        .AllowWrite = Not tag.AllowWrite
        ' .EnableDeadBand = tag.EnableDeadBand
        ' .EnableDriverEvent = tag.EnableDriverEvent
        .DocString = tag.DocString
        .Name = tag.Name
        .N1 = tag.N1
        .N2 = tag.N2
        .N3 = tag.N3
        .N4 = offset
        If tag.Kind <> "CD" Then
            .Links.CreateLink "Value", "Aquisicao.IEC61850." & tag.Folder & "." & tag.Name & ".Value", 0
        End If
    End With
End Sub