无锡老酒:怎样利用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 [" & 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.Fields(11).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

直接将EXCEL导入ACCESS中即可.