How to import multiple sheet of excel into Access database to a diffrent tables??
 

Hello
i am lookinng some unsocumented feture (earlier) of EXCEL,ACCESS to
achive the following functionality.

1. Excel file is having multiple sheets say 10:
2. i want to download all the data into an Access database
3. output should have sheetname--tablename.

I tried the below code (Which was an undocumented earlier)

DoCmd.TransferSpreadsheet transfertype:=acImport, _
tablename:="tblsheet1", _
FileName:="A:\exceldata\excelbook1to10.xls",
Hasfieldnames:=False, _
Range:="Sheet1!", SpreadsheetType:=9

When i executed from the VBA code, it is displaying the following
error,
Thanks in advance.
baka@tokyo
japan

Execution error
Worksheet format variable value is not correct... etc(translated from
japanese )

Does any one show me the way to get all the sheet should becomes
tables in access

Here is the code i was using in VBE (part of the code is changed for
web viewing purpose)

------------------------------------------
Private Sub import_Click()
catlog.ActiveConnection = CurrentProject.Connection

For Each tble In catlog.Tables
If tble.Name = "table001" Then
MsgBox "table001 allreday exists", vbOKOnly +
vbInformation, "confirm please"
Exit Sub
End If
Next tble
, the below 5 lines code is giving an error
DoCmd.TransferSpreadsheet transfertype:=acImport, _
tablename:="tblsheet1", _
FileName:="A:\exceldata\excelbook1to10.xls",
Hasfieldnames:=False, _
Range:="Sheet1!", SpreadsheetType:=9


'
' the below code working for a first sheet
' For Each tble In catlog.Tables
' If tble.Name = "table001" Then
' MsgBox "table001 allreday exists", vbOKOnly +
vbInformation, "confirm please"
' Exit Sub
' End If
' Next tble
' DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9,
"table001", "A:\exceldata\excelbook1to10.xls", True, ""

End Sub

-----------------------------------------