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

转载于:https://www.cnblogs.com/RobotTech/archive/2009/05/27/1490593.html