vba data to input tool

public Sub InserDb()

 

dim maxrow as integer

dim maxcol as integer

dim ddl as string

dim recode as string

dim recodes as string

dim sheetname as string

dim tablename as string

 

sheetname = "data"

tablename  = sheets(sheetname).cells(1,2).value

maxrow = sheets(sheetname).cells(rows.count,1).end(xlup).row

maxcol = sheets(sheetname).cells(2,columns.count).end(xltoleft).column

 

for i = 1 to maxcol

　　if i = 1 then

　　　　ddl = sheets(sheetname).cells(2,i).value

　　else

　　　　ddl = ddl + "," + sheets(sheetname).cells(2,i).value

　　end if

next

 

for j = 3 to maxrow

　　for k = 1 to maxcol

　　　　if k = 1 then

　　　　　　recode = "(" & "'" & sheets(sheetname).cells(j,k).value & "'"

　　　　else

　　　　　　if sheets(sheetname).cells(j,k).value = "" then

　　　　　　　　recode = recode & "," & "NULL"

　　　　　　else

　　　　　　　　recode = recode & "," & "'" & sheets(sheetname).cells(j,k).value & "'"

　　　　　　end if

　　　　end if

　　next

　　sheets("output").cells(j-1,1).value = "insert into" & tablename & "(" & dll & ")" & "values" & recode + ");"

next

 

sheets("output").cells(1,1).value = "TRUNCATE" & tablename & ";"

sheets("output").select

end sub