Excel VB 宏 示例代码

Sub T_IC_HIERARCHICAL_DATA_I()
    Dim insert As String
    Dim isExists As String

    Dim node_ID As String
    Dim category As String
    Dim code As String
    Dim name As String
    Dim alia As String
    Dim desc As String
    Dim remarks As String
    Dim parent_ID As String
    Dim map_Path As String

    Dim path_ID As String
    Dim depth As String
    Dim sort_Index As String

    Dim flag As String
    Dim is_Deleted As String

    For i = 2 To Sheet1.UsedRange.Rows.Count
        If Trim(Sheet1.Cells(i, 1)) <> "" Then
            node_ID = "'" & Trim(Sheet1.Cells(i, 1)) & "'"
            category = "'" & Sheet1.Cells(i, 2) & "'"
            code = "'" & Sheet1.Cells(i, 3) & "'"
            name = "'" & Sheet1.Cells(i, 4) & "'"
            alia = "'" & Sheet1.Cells(i, 5) & "'"
            desc = "'" & Sheet1.Cells(i, 6) & "'"
            remarks = "'" & Sheet1.Cells(i, 7) & "'"
            parent_ID = "'" & Sheet1.Cells(i, 8) & "'"
            map_Path = "'" & Sheet1.Cells(i, 9) & "'"

            path_ID = Sheet1.Cells(i, 10)

            If Trim(Sheet1.Cells(i, 11)) <> "" Then
                depth = Trim(Sheet1.Cells(i, 11))
            Else
                depth = "NUll"
            End If

            If Trim(Sheet1.Cells(i, 12)) <> "" Then
                sort_Index = Trim(Sheet1.Cells(i, 12))
            Else
                sort_Index = "NUll"
            End If

            flag = "'" & Sheet1.Cells(i, 13) & "'"
            is_Deleted = "'" & Sheet1.Cells(i, 14) & "'"

            isExists = "IF not exists( select 1 from T_IC_HIERARCHICAL_DATA where NODE_ID=" & node_ID & ")"

            insert = isExists & "INSERT INTO [dbo].[T_IC_HIERARCHICAL_DATA] (NODE_ID, APP_ID, CATEGORY, LOWERED_CATEGORY, CODE, LOWERED_CODE, [NAME], [ALIAS], [DESC], REMARKS, PARENT_ID, EFFECTIVE_START_DATE, EFFECTIVE_END_DATE, MAP_PATH, PATH_ID, DEPTH, SORT_INDEX, FLAG, IS_DELETED, VERSION_NO, TRANSACTION_ID, CREATED_BY, CREATED_TIME, LAST_UPDATED_BY, LAST_UPDATED_TIME) VALUES (" _
                      & node_ID & ", 'd031ded7-e18c-4fef-8c2e-a30fc30f9df1'" & ","

            insert = insert & category & "," & LCase(category) & ","
            insert = insert & code & "," & LCase(code) & ","
            insert = insert & name & ","
            insert = insert & alia & ","
            insert = insert & desc & ","
            insert = insert & remarks & ","
            insert = insert & parent_ID & ","

            insert = insert & "CONVERT(DATETIME,'20000101',112), CONVERT(DATETIME,'99981231',112),"

            insert = insert & map_Path & ","

            insert = insert & path_ID & ","
            insert = insert & depth & ","
            insert = insert & sort_Index & ","

            insert = insert & flag & ","
            insert = insert & is_Deleted & ","

            insert = insert & "1, '*', 'CLSYSTEM', GETDATE(), 'CLSYSTEM', GETDATE())"
            insert = Replace(insert, "'NULL'", "NULL")
            insert = Replace(insert, Chr(10), " ")

            'Sheet4.Unprotect ("123@abc")
            Sheet1.Cells(i, 15) = insert
            'Sheet4.Protect ("123@abc")
        End If
    Next
MsgBox "SUCCESS!"
End Sub