VBA代码：将Excel数据批量导入Access数据库

Dim conn As ADODB.Connection Set conn = New ADODB.Connection conn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=E:\检测设备\力学\VBA测试\New.mdb"

Dim cmd As ADODB.Command Dim i As Long For i = 1 To 948 Set cmd = New ADODB.Command cmd.ActiveConnection = conn cmd.CommandText = "insert into OriginalData(TestNo,littleNo,Name,TheValue,UserOrResultParam) values (?,?,?,?,?)"

Dim currentRow As Range
Set currentRow = Sheets("过程数据").Rows(i)

currentRow.Cells(1, 1).Value = i   '复制序号
currentRow.Cells(1, 2).Value = Xha                                       '复制TestNo
currentRow.Cells(1, 3).Value = Val(Sheets("参考数据").Cells(i, 3).Value) + 0.05  '复制时间
currentRow.Cells(1, 4).Value = valueA * Val(Sheets("参考数据").Cells(i, 4).Value) '复制力
currentRow.Cells(1, 5).Value = Val(Sheets("参考数据").Cells(i, 5).Value) + 0.05 '复制位移

cmd.Parameters.Append cmd.CreateParameter("value0", adVarChar, adParamInput, 255, i) 'id
cmd.Parameters.Append cmd.CreateParameter("value1", adVarChar, adParamInput, 255, Xha) 'TestNo
cmd.Parameters.Append cmd.CreateParameter("value2", adVarChar, adParamInput, 255, currentRow.Cells(1, 3).Value) 'PlayTime
cmd.Parameters.Append cmd.CreateParameter("value3", adVarChar, adParamInput, 255, currentRow.Cells(1, 4).Value) 'LoadValue
cmd.Parameters.Append cmd.CreateParameter("value4", adVarChar, adParamInput, 255, currentRow.Cells(1, 5).Value) 'PositionValue
cmd.Parameters.Append cmd.CreateParameter("value5", adVarChar, adParamInput, 255, 3) 'DeformSwitch
cmd.Parameters.Append cmd.CreateParameter("value6", adVarChar, adParamInput, 255, 1) 'CtrlStep
cmd.Execute

Next i

MsgBox "第一组过程数据更新完成"

Dim j As Long Xhb = Xha + 1 For j = 949 To 1896 Set cmd = New ADODB.Command cmd.ActiveConnection = conn cmd.CommandText = "insert into OriginalData(TestNo,littleNo,Name,TheValue,UserOrResultParam) values (?,?,?,?,?)"

Dim currentRow As Range
Set currentRow = Sheets("过程数据").Rows(j)

currentRow.Cells(1, 1).Value = j
currentRow.Cells(1, 2).Value = Xhb
currentRow.Cells(1, 3).Value = Val(Sheets("参考数据").Cells(j, 3).Value) + 0.04
currentRow.Cells(1, 4).Value = ValueB * Val(Sheets("参考数据").Cells(j, 4).Value)
currentRow.Cells(1, 5).Value = Val(Sheets("参考数据").Cells(j, 5).Value) + 0.04

cmd.Parameters.Append cmd.CreateParameter("value0", adVarChar, adParamInput, 255, j) 'id
cmd.Parameters.Append cmd.CreateParameter("value1", adVarChar, adParamInput, 255, Xhb) 'TestNo
cmd.Parameters.Append cmd.CreateParameter("value2", adVarChar, adParamInput, 255, currentRow.Cells(1, 3).Value) 'PlayTime
cmd.Parameters.Append cmd.CreateParameter("value3", adVarChar, adParamInput, 255, currentRow.Cells(1, 4).Value) 'LoadValue
cmd.Parameters.Append cmd.CreateParameter("value4", adVarChar, adParamInput, 255, currentRow.Cells(1, 5).Value) 'PositionValue
cmd.Parameters.Append cmd.CreateParameter("value5", adVarChar, adParamInput, 255, 3) 'DeformSwitch
cmd.Parameters.Append cmd.CreateParameter("value6", adVarChar, adParamInput, 255, 1) 'CtrlStep
cmd.Execute

Next j

conn.Close ' 关闭数据库连接 Set conn = Nothing ' 清空对象 MsgBox "第二组过程数据更新完成"