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, "Yams"
End Sub
使用范例如下:將 C:\book1.xls 中的 Sheet1 导入 C:\Test.mdb 成为 TestTable
Private Sub Command1_Click()
'''' 在FROM 中添加DATA控件
''''使DATA NAME : DATA1EXCEL
'''' CONNECT: Excel 8.0;
'''' 打开相应的文件的表
Dim Db As Database, Rs As Recordset '定义为公用变量
Dim TableNew As TableDef
Dim SQLstring, SQLfield, SQLvalue As String
Dim i, n As Integer
Set Db = Workspaces(0).OpenDatabase("C:\BROW\TEST.MDB") '''MDB 正确的文件路径
Db.Execute ("DROP TABLE TestTMP ") ''调试使用
Set TableNew = Db.CreateTableDef("TestTMP") '''临时的新表名
For i = 0 To DataEXCEL.Recordset.Fields.Count - 1 '''通过循环获得字短名,类型,长度等
With TableNew
.Fields.Append .CreateField(DataEXCEL.Recordset(i).Name, _
DataEXCEL.Recordset(i).Type, DataEXCEL.Recordset(i).Size)
End With
Next
Db.TableDefs.Append TableNew
Set Rs = Db.OpenRecordset("testtmp", dbOpenDynaset)
DataEXCEL.Recordset.MoveFirst
For i = 1 To DataEXCEL.Recordset.RecordCount
With Rs
.AddNew
For n = 0 To DataEXCEL.Recordset.Fields.Count - 1 '''通过这样的循环才能保证类型,长度匹配,否则很难控制错误
Rs(n) = DataEXCEL.Recordset(n)
Next
.Update
End With
DataEXCEL.Recordset.MoveNext
Next
Set Db = Nothing
Set TableNew = Nothing
End Sub