|
本过程完成将Excel2007中的工作表批量导入到Access2007数据库中
延用导入的工作表名称
Private Sub ExportExcelSheetToAccess()
Dim sSheetName As String 'Excel表名
Dim sAccessTable As String 'Access 表名
Dim sAccessDBPath As String 'Access 数据库路径及名称
sAccessDBPath = "e:\test.accdb" 此处填写Access 数据库路径及名称
Set con = CreateObject("ADODB.Connection")
con.Open "Provider=Microsoft.ACE.OLEDB.12.0;Extended Properties='Excel 12.0;HDR=yes;IMEX=1';Data Source=" & ThisWorkbook.FullName
For Each wsheet In Worksheets
sSheetName = wsheet.Name
sAccessTable = wsheet.Name
mysql = "Select * into " & sAccessTable & " in '" & sAccessDBPath & "' FROM [" & sSheetName & "$]"
con.Execute (mysql)
Next wsheet
End Sub |
|