Import Workbook with multiple worksheets 1

[sap1958]

Option Compare Database

Function TransferMultiples()
Dim strDir As String, strFile As String
strDir = "C:\pcsas_export_files\Test\"
strFile = Dir(strDir & "*.xls")
While strFile <> ""
DoCmd.TransferSpreadsheet acImport, 10, "invoiceg", strDir & strFile, True, ""
strFile = Dir
Wend
End Function

When I use the above named function it will import all workbooks into access. The issue is it will only import sheet1 of each workbook. Lets say I have the following workbook names and worksheets:
workbook1(sheet1,sheet2,sheet3)
workbook2(mysheet1,mysheet2,mysheet3)
How can I modify the code to import all worksheets into their own table names in access

 

[MazeWorX]

I believe you have to open the workbook to get all of the sheet names and .... yes ... had a link to some code (had to find it again )

http://www.accessmvp.com/KDSnell/EXCEL_Import.htm#ImpAllWkshts

HTH << MaZeWorX >> "I have not failed I have only found ten thousand ways that don't work" <<Edison>>