vb将数据导入access
- 1、下载文档前请自行甄别文档内容的完整性，平台不提供额外的编辑、内容补充、找答案等附加服务。
- 2、"仅部分预览"的文档,不可在线预览部分如存在完整性等问题,可反馈申请退款(可完整预览的文档不适用该条件!)。
- 3、如文档侵犯您的权益，请联系客服反馈,我们会尽快为您处理(人工客服工作时间：9:00-18:30)。
Private Function ImportFromExcel(excelFile As String) As Long
Dim xlApp As Excel.Application
Dim xlBook As Excel.Workbook
Dim xlSheet As Excel.Worksheet
Dim rst As New ADODB.Recordset
Dim nCount As Long, strCardNo As String
On Error GoTo errH
Set xlApp = New Excel.Application
Set xlBook = xlApp.Workbooks.Open(excelFile) '打开EXCEL文件
Set xlSheet = xlBook.Sheets(1) '打开第一页（sheet）
ImportFromExcel = 0
'打开数据库
rst.Open "...", pConn, adOpenKeyset, adLockOptimistic, adCmdText
'开始导入。导入过程检查数据是否有重复。
nCount = 1
Do
strCardNo = Trim(xlSheet.Cells(nCount, 1))
'这里根据第一列数据为空判断记录结束
If Len(strCardNo) = 0 Then
ImportFromExcel = nCount - 1
Exit Do
End If
If Not rst.EOF Then rst.Find ("STCardNO='" & strCardNo & "'")
If rst.EOF Then
rst.AddNew
rst("STCardNO") = Format(strCardNo, "0000000000")
...
rst.Update
Else
If MsgBox("卡号：" & strCardNo & " 已经存在。" & vbCrLf & "选择“重试”将忽略并继续导入余下的数据，“取消”将放弃导入。", vbExclamation + vbRetryCancel + vbDefaultButton2, "卡号重复") = vbCancel Then
ImportFromExcel = nCount - 1
Exit Do
End If
End If
nCount = nCount + 1
ImportFromExcel =nCount
Loop
'销毁对象释放资源
rst.Close
Set rst = Nothing
Set xlSheet = Nothing
Set xlBook = Nothing
xlApp.Quit '关闭Excel Set xlApp = Nothing
Exit Function
errH:
ImportFromExcel = -1 End Function