怎样把excel的内容导入access数据库中,谢谢!!!

[wx1009]

现在正在做把excel导入access的程序,请各位好心人指点迷津,谢谢!

[wx1009]

好心人们，给点帮助，很急的。全靠你们了

[深渊的水影]

报什么错

[wx1009]

我的全部代码：
Private Sub openExcel_Click()
Dim cn As New ADODB.Connection
Dim rs As New ADODB.Recordset
Dim connectionstring As String
Dim sql As String
connectionstring = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & App.Path & "\RMData.mdb;Persist Security Info=False"
cn.Open (connectionstring)
cn.CursorLocation = adUseClient
sql = "select * from NewData"
rs.CursorLocation = adUseClient
rs.Open sql, cn, 3, 2

Dim objExcel As Excel.Application
Dim objBook As Excel.Workbook
Dim objSheet As Excel.Worksheet
Dim strPicName As String
On Error Resume Next

CDlog.CancelError = True 'cdlInPut为CommonDialog控件

'属性DialogTitle是要弹出的对话框的标题
CDlog.DialogTitle = "选择 Microsoft Excel 文件"

'缺省的文件名为空
CDlog.FileName = ""
CDlog.Filter = "Excel 文件 (*.xls)|*.xls"

'Flags属性的用法依据不同的对话框而变
CDlog.Flags = cdlOFNHideReadOnly + cdlOFNFileMustExist
CDlog.ShowOpen
If Err = cdlCancel Then Exit Sub
strPicName = CDlog.FileName
Set objExcel = CreateObject("Excel.Application")
Set objBook = objExcel.Workbooks.Open("strPicName")
objExcel.Visible = False

Set objSheet = objBook.Worksheets(1)
Dim nRows As Integer
Dim i As Integer

nRows = objExcel.Sheets(1).UsedRange.Rows.Count '求得行數
For i = 1 To nRows
rs.AddNew
rs(2) = objSheet.Cells(i, 1)‘rs(2)指rs的第三列字段
rs(3) = objSheet.Cells(i, 2)
rs(4) = objSheet.Cells(i, 3)
rs(5) = objSheet.Cells(i, 4)
rs(6) = objSheet.Cells(i, 5)
rs.Update
rs.movenext
Next
rs.Close
cn.Close

End Sub
跟踪后发现nRows为零，不知问题在哪儿，谢谢指教！！！！

[云路]

把EXCEL的每个单元格读出来，然后组成SQL语句往ACCESS里写。

[wx1009]

不好意思,,能不能给个完全的代码,小弟的水平是在汗颜......谢谢

[of123]

用下面的函数。需要引用 Microsoft DAO 3.6 library。

此函数共有四个参数：
1、sSheetName：要导出资料的文件名称 (Sheet name)，例如 Sheet1
2、sExcelPath：要导出资料的 Excel 档案路径名称 (Workbook path)，例如 C:\book1.xls
3、sAccessTable：要导入的 Access Table 名称，例如 TestTable
4、sAccessDBPath：要导入的 Access 档案路径名称，例如 C:\Test.mdb

函数以下：

Private Sub ExportExcelSheetToAccess(sSheetName As String, sExcelPath As String, sAccessTable As String, sAccessDBPath As String)
Dim db As Database
Dim rs As Recordset
Set db = OpenDatabase(sExcelPath, True, False, "Excel 5.0")
Call db.Execute("Select * into [;database=" & sAccessDBPath & "]." & sAccessTable & " FROM [" & sSheetName & "$]")
MsgBox "Table exported successfully.", vbInformation
End Sub

使用范例如下：將 C:\book1.xls 中的 Sheet1 导入 C:\Test.mdb 成为 TestTable

ExportExcelSheetToAccess "Sheet1", "C:\book1.xls", "TestTable", "C:\Test.mdb"

[wx1009]

能不能给个用ado的，谢谢了

[wx1009]

Dim objExcel As Excel.Application
Dim objBook As Excel.Workbook
Dim objSheet As Excel.Worksheet
Set objExcel = CreateObject("Excel.Application")
Set objBook = objExcel.Workbooks.Open("" & App.Path & "\WeatherData.xls")
objExcel.Visible = False
Set objSheet = objBook.Worksheets(1)
dim nRows as Integer
dim i as Integer

nRows =objExcel.Sheets(1).UsedRange.Rows.Count
上面的代码是什么意思啊，哪位强人指点一下。
nRows =objExcel.Sheets(1).UsedRange.Rows.Count，这行代码在我程序里有错误，我的excel文件只有一页，名称为WeatherData。好心人，帮帮忙，很急的。