如何将多个Excel工作表批量追加入Access

Sub GetData_From_Excel_Sheet()

'Step 1: Declare your variables
Dim MyConnect As String
Dim MyRecordset As ADODB.Recordset
Dim MyTable As ADODB.Recordset
Dim MySQL As String

'Step 2: Declare your connection string
MyConnect = "Provider=Microsoft.ACE.OLEDB.12.0;" & "Data Source=C:\OffTheGrid\Chapter15_SampleFile.xlsm;" & "Extended Properties=Excel 12.0"

'Step 3: Build your SQL statement
MySQL = "SELECT * FROM [SampleData$]" & "WHERE Region ='NORTH'"

'Step 4: Instantiate and specify your recordset
Set MyRecordset = New ADODB.Recordset
MyRecordset.Open MySQL, MyConnect, adOpenStatic, adLockReadOnly

'Step 5: Instantiate and specify your Access table
Set MyTable = New ADODB.Recordset
MyTable.Open "ExcelFeed", CurrentProject.Connection,adOpenDynamic, adLockOptimistic

'Step 6: Loop through each record and add to the table
Do Until MyRecordset.EOF
MyTable.AddNew
MyTable!ActiveRegion = MyRecordset!Region
MyTable!ActiveMarket = MyRecordset!Market
MyTable!Product = MyRecordset!Product_Description
MyTable!Revenue = MyRecordset!Revenue
MyTable!Units = MyRecordset!Transactions
MyTable![Dollar Per Unit] = MyRecordset![Dollar Per Transaction]
MyTable.Update
MyRecordset.MoveNext
Loop

End Sub