利用VB将EXCEL中的数据导入ACCESS.doc

Dim extend_str As String Dim execl_cnn As New ADODB.Connection Dim execl_rst As New ADODB.Recordset Dim execl_count_rst As New ADODB.Recordset 纪录数量 Dim execl_field_rst As New ADODB.Recordset Dim execl_strconn As String Dim execl_strcmd As String Dim execl_count_str As String Dim insertStr As String 操作oracle数据库的记录集 Dim rst As ADODB.Recordset CommonDialog1.ShowOpen extend_str = Right(CommonDialog1.FileName, 3) If (extend_str = xls) Then execl_cnn.Open Provider=Microsoft.Jet.OLEDB.4.0; & _ Data Source= & CommonDialog1.FileName & _ ;Extended Properties=Excel 8.0; ElseIf (extend_str = dbf) Then execl_cnn.ConnectionString = Provider=MSDASQL.1;Driver=Microsoft Visual Foxpro Driver;SourceType=DBF;DBQ= & getDir(CommonDialog1.FileName) execl_cnn.Open End If If (extend_str = xls) Then execl_strcmd = SELECT * from Sheet1$ execl_count_str = SELECT count(*) from Sheet1$ execl_field_rst.Open execl_count_str, execl_cnn If (execl_field_rst.Fields(0).Value = 0) Then execl_strcmd = SELECT * from & getFileNoExt(getfile(CommonDialog1.FileName) & $ execl_count_str = SELECT count(*) from & getFileNoExt(getfile(CommonDialog1.FileName) & $ End If execl_field_rst.Close ElseIf (extend_str = dbf) Then execl_strcmd = SELECT * from & CommonDialog1.FileName execl_count_str = SELECT count(*) from & CommonDialog1.FileName End If execl_rst.Open execl_strcmd, execl_cnn execl_count_rst.Open execl_count_str, execl_cnn If (CommonDialog1.FileName ) Then 进度条设置 ProgressBar1.Min = 0 ProgressBar1.max = execl_count_rst.Fields(0).Value If (execl_rst.RecordCount) Then 如果有记录 If (Option1.Value) Then 如果是仪器总库 If (execl_rst.Fields.Count = 8) Then Set rst = yg_gain_table_recordset(yg_device) execl_rst.MoveFirst Do While (Not execl_rst.EOF) rst.AddNew rst.Fields(0).Value = yg_getNextId(yg_device_id) rst.Fields(1).Value = execl_rst.Fields(0).Value & rst.Fields(2).Value = execl_rst.Fields(1).Value & rst.Fields(3).Value = execl_rst.Fields(2).Value & rst.Fields(4).Value = execl_rst.Fields(3).Value & rst.Fields(5).Value = execl_rst.Fields(4).Value & rst.Fields(6).Value = execl_rst.Fields(5).Value & rst.Fields(7).Value = execl_rst.Fields(6).Value & rst.Fields(8).Value = execl_rst.Fields(7).Value & rst.Update execl_rst.MoveNext ProgressBar1.Value = ProgressBar1.Value + 1 DoEvents Loop yg_free_recordset rst Else MsgBox 结构不一致 Exit Sub End If End If If (Option2.Value) Then 如果是外借仪器 If (execl_rst.Fields.Count = 12) Then Set rst = yg_gain_table_recordset(yg_out_device) execl_rst.MoveFirst Do While (Not execl_rst.EOF) rst.AddNew rst.Fields(0).Value = yg_getNextId(yg_out_device_id) rst.Fields(1).Value = execl_rst.Fields(0).Value & rst.Fields(2).Value = execl_rst.Fields(1).Value & rst.Fields(3).Value = execl_rst.Fields(2).Value & rst.Fields(4).Value = execl_rst.Fields(3).Value & rst.Fields(5).Value = execl_rst.Fields(4).Value & rst.Fields(6).Value = execl_rst.Fields(5).Value & rst.Fields(7).Value = execl_rst.Fields(6).Value & rst.Fields(8).Value = execl_rst.Fields(7).Value & rst.Fields(9).Value = execl_rst.Fields(8).Value & rst.Fields(10).Value = execl_rst.Fields(9).Value & rst.Fields(11).Value = execl_rst.Fields(10).Value & rst.Fields(12).Value = execl_rst.Fiel