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_)"

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