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

---------------------------------------------------------------最新资料推荐------------------------------------------------------ 利用VB将EXCEL中的数据导入ACCESS

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

1/ 4

[ 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 =

3/ 4