- 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
来源: http://www.bubuko.com/infodetail-3004619.html