|
|
Sub qqq1()
Dim conn As New ADODB.Connection
Dim Rs As New ADODB.Recordset
Dim i%, strTemp$, RowNum%, K%
Dim wkSheet As Worksheet
Dim bb%
If MsgBox("确认要导入数据吗？", vbYesNo) = 7 Then Exit Sub
conn.Open "Driver={SQL Server};server=192.168.1.111;uid=sa;pwd=sa;database=ASY;"
Application.ScreenUpdating = False
Set wkSheet = Worksheets("原始数据")
RowNum = Range("c65536").End(xlUp).Row
On Error GoTo 9
conn.BeginTrans
K = 0
For i = 2 To RowNum
If wkSheet.Cells(i, 2).Value <> 0 Then
'拼写INSERT语句的SQL语句
strTemp = "insert into 物资信息 (编号,材质楞型,楞别,班组,类别,长度,宽度,门幅,路数,订单数,入库数) "
strTemp = strTemp & " values( '" & wkSheet.Cells(i, 2).Value & "' , '" & _
wkSheet.Cells(i, 3).Value & "' , '" & _
wkSheet.Cells(i, 4).Value & "' , '" & _
wkSheet.Cells(i, 5).Value & "' , '" & _
wkSheet.Cells(i, 6).Value & "' , '" & _
wkSheet.Cells(i, 7).Value & "' , '" & _
wkSheet.Cells(i, 8).Value & "' , '" & _
wkSheet.Cells(i, 9).Value & "' , '" & _
wkSheet.Cells(i, 10).Value & "' , '" & _
wkSheet.Cells(i, 11).Value & "' , '" & _
wkSheet.Cells(i, 12).Value & "')"
'执行INSERT语句
conn.Execute strTemp
K = K + 1
End If
Next
conn.CommitTrans '结束插入操作的事务
ThisWorkbook.Save
conn.Close '关闭链接
Set Rs = Nothing '释放内存
Set conn = Nothing '释放内存
Set wkSheet = Nothing
Application.ScreenUpdating = True
MsgBox "导入完毕！" & Chr(10) & Chr(10) & "已经插入的条数：" & K
Exit Sub
9:
'Conn.RollbackTrans
MsgBox Err.Description & "第" & i & "行 导入出错"
End If
End Sub
[ 本帖最后由 2217918 于 2011-8-2 09:27 编辑 ] |
|
沪公网安备 31011702000001号 沪ICP备11019229号-2
发表于 2011-7-30 20:23
楼主