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

MGallery

Michael Alexander在其The Excel Analyst's Guide to Access  中给出了如何将单个Excel工作表导入Access, 代码如下。

如果要将一个文件中的多个工作表导入该如何修改一下呢？盼望高人指点。

Sub GetData_From_Excel_Sheet()Dim MyConnect As String
Dim MyRecordset As ADODB.Recordset
Dim MyTable As ADODB.Recordset
Dim MySQL As String


MyConnect = "Provider=Microsoft.ACE.OLEDB.12.0;" & "Data Source=C:\OffTheGrid\Chapter15_SampleFile.xlsm;"
& "Extended Properties=Excel 12.0"

MySQL = "SELECT * FROM [SampleData$]" & "WHERE Region ='NORTH'"
Set MyRecordset = New ADODB.Recordset

MyRecordset.Open MySQL, MyConnect, adOpenStatic, adLockReadOnly
Set MyTable = New ADODB.RecordsetMyTable.Open "ExcelFeed", CurrentProject.Connection,adOpenDynamic, adLockOptimistic
Do Until MyRecordset.EOF
MyTable.AddNewMyTable!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

roych

修改：MySQL = "SELECT * FROM [SampleData$]" & "WHERE Region ='NORTH'"
为：
MySQL = "SELECT * FROM [表1$] WHERE Region ='NORTH' Union  all SELECT * FROM [表2$] WHERE Region ='NORTH'"
多加几个Union all 和"SELECT * FROM [表1$] WHERE Region ='NORTH' "即可。

zhangjimfu

http://club.excelhome.net/thread-758903-1-2.html

供参考

MGallery

zhangjimfu 发表于 2011-12-26 08:49
http://club.excelhome.net/thread-758903-1-2.html

供参考

多谢多谢。

易兴中

循序渐进，慢慢学习。{:soso_e113:}