除了access可以从excel直接导入(有点不符合本人观点)外,
可以用VB来操作。
两个注意点:
1、先查询记录在access是否存在,存在了就不用添加。
2、打开时,用键集游标adOpenKeyset,不然那个查询的记录集的个数是-1,为判断是否存在记录造成困惑。
Private Sub Command1_Click()
Dim cn As New ADODB.Connection
Dim rs As New ADODB.Recordset
Dim i As Integer, j As Integer
Dim xl As New Excel.Application
Dim book As Excel.Workbook
Dim sheet As Excel.Worksheet
Set book = xl.Workbooks.Open("D:\etoa\2.xls") '打开excel,
Set sheet = book.Worksheets(1)
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\eToa\Station.mdb;Persist Security Info=False"
For i = 1 To 282
'查询
rs.Open "select * from station where StationNumber='" & Trim(sheet.Cells(i, 1)) & "'", cn, adOpenKeyset, adLockOptimistic
'是否有记录,有记录就不添加
If rs.RecordCount = 0 Then '没有记录,添加
rs.AddNew
rs.Fields(1) = sheet.Cells(i, 1) '注意fields索引是以0开始,excel是以1开始
rs.Fields(2) = sheet.Cells(i, 2)
rs.Fields(3) = sheet.Cells(i, 3)
rs.Fields(4) = sheet.Cells(i, 4)
rs.Fields(5) = sheet.Cells(i, 5)
rs.Fields(6) = sheet.Cells(i, 6)
rs.Fields(7) = sheet.Cells(i, 7)
rs.Update
Else
List1.AddItem sheet.Cells(i, 1) '这是没有被添加的记录
End If
rs.Close
Next i
MsgBox "导入完成"
book.Close '退出
xl.Quit
cn.Close
End Sub