请教怎么在access中用VBA导入excel数据到access库

Question

如题所述

Answer 1

下面是我自己写的读取Excel文件内容写入Sql数据库的代码，和写入Access完全一样的。
比较简单，可用，测试过了
记得给分阿，以身相许就算了

'通用对话框打开文件
CommonDialog1.CancelError = False
CommonDialog1.Filter = "XLS|*.XLS "
CommonDialog1.FileName = " "
CommonDialog1.ShowOpen
ExcelName = CommonDialog1.FileName
If ExcelName = " " Then Exit Sub
'创建一个Excel应用对象
Set xlsApp = CreateObject( "Excel.Application ")
'指定要打开的文档
Set xlsWorkbooks = xlsApp.Workbooks.Open(ExcelName)
'指定要打开的工作表
Set xlsWorksheets = xlsWorkbooks.Worksheets(1)
'读取文件标题项内容
dr_KCMC = Trim(xlsWorksheets.Cells(7, 2))
....

Do
'读取文件内容
dr_XSXH = Trim(xlsWorksheets.Cells(i, 2))
dr_XSXM = Trim(xlsWorksheets.Cells(i, 3))
dr_KCCJ = Trim(xlsWorksheets.Cells(i, 4))

If dr_JLXH = " " Or dr_XSXH = " " Or dr_XSXM = " " Then
Exit Do
End If
rsXSCJ.AddNew
rsXSCJ.Fields( "xsxh ").Value = dr_XSXH
rsXSCJ.Fields( "xsxm ").Value = dr_XSXM
.....
rsXSCJ.Update
RecordCount1 = RecordCount1 + 1
i = i + 1
Loop While (dr_JLXH <> " " And dr_XSXH <> " " And dr_XSXM <> " " And dr_XSXM <> " ")