I am assuming that all 22 "Sheets" are 22 worksheets all in the same
workbook. I tested the function below and it appears to work. Someone may
have a more elegant way to handle the Range parameter of the
TransferSpreadsheet method, but this gets the job done.
Public Function ImportSheets() As Boolean
Dim xl As New Excel.Application
Dim wks As Worksheet
Dim wkb As Workbook
Set wkb = xl.Workbooks.Open("H:\testImport.xls")
For Each wks In wkb.Sheets
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9,
"Test5", wkb.FullName, _
True, wks.Name & "!" & "A1:IV65536"
Next wks
ImportSheets = True
End Function
If the 22 sheets are all in different workbooks, you can try putting them in
one directory and then using the FileSystemObject to iterate through each
workbook in the directory. You could also just merge them into one
workbook.
--
David Lloyd
MCSD .NET
http://LemingtonConsulting.com
This response is supplied "as is" without any representations or warranties.
I have about 22 excel sheets that I would like to put into one access table,
is there an easy way to do this?
Thanks
