Importing multiple work sheets from a single excel Workbook

[vb_Canada]

I made a macro in Access and then converted that to a VB script. Now, when I
run this script, it only imports the data set from the first excel work sheet
to all of the tabels that it creates. Once it worked and filled all the
different tables with all of the data but not a regular basis.

I am certain one coudl add looping and some otjher more automated functions
but I would like to get this small step working first.

Sub Import_Multiple_Excell_WS()


DoCmd.TransferSpreadsheet acImport, , "ws1", "C:\fName.xls", True, "A1:BM3"
DoCmd.TransferSpreadsheet acImport, , "ws2", "C:\fName.xls", True, "A1:BM6"
DoCmd.TransferSpreadsheet acImport, , "ws3", "C:\fName.xls", True, "A1:BL6"
DoCmd.TransferSpreadsheet acImport, , "ws4", "C:\fName.xls", True, "A1:BI98"
DoCmd.TransferSpreadsheet acImport, , "ws5", "C:\fName.xls", True, "A1:CD5"
DoCmd.TransferSpreadsheet acImport, , "ws6", "C:\fName.xls", True, "A1:CJ12"
DoCmd.TransferSpreadsheet acImport, , "ws7", "C:\fName.xls", True, "A1:BN2"
DoCmd.TransferSpreadsheet acImport, , "ws8", "C:\fName.xls", True, "A1:BL15"

End Sub

any suggetions?

 

[Klatuu]

You have to include the name of the worksheet; otherwise, it defaults to the
first sheet in the book.

DoCmd.TransferSpreadsheet acImport, , "ws1", "C:\fName.xls", True,
"SheetNameHere!A1:BM3"