Hi Hari,

You don't have to specify a cell range: you can specify a worksheet.
Just give the sheet name a screamer:

docmd.TransferSpreadsheet acLink, acSpreadsheetTypeExcel8, "tblT",
"C:\Temp\T97\MyWorkbook.xls",True, "Sheet1!"

--
John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.

Hi Hari,

some aircode to import all the sheets from your file

'************************************
Function MySheetImport()
Dim XLapp As Object
Dim XLFile As String
Dim XLSheet As String
Dim XLRange As String
Dim TableName As String
Dim z As Integer
Dim SheetCount As Integer

Set XLapp = GetObject(, "Excel.Application")

XLapp.Visible = True        'Excel is visible!! or if False not visible!!
XLFile = "c:\temp.xls"        'Your File
TableName = "Employees"        'Table to import into
XLRange = "!a1:z10"        'Specifies the area to be imported

Set XLwb = XLapp.Workbooks.Open(XLFile)    'Opens your file in Excel

'if you want to import all sheets in your Excel file into one table use the
following 6 lines of code

'if you need only sheet 3, remove the for-next construct, keep the 3 lines
of code within and change the code from .Sheets(z).Name
'  to .Sheets(3).Name

SheetCount = XLapp.ActiveWorkbook.Sheets.Count        'Gives you the total number
of sheets
For z = 1 To SheetCount
   XLSheet = XLapp.ActiveWorkbook.Sheets(z).Name    'get name of sheet number z
   XLSheet = XLSheet & XLRange                'add range to sheetname
   DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel8, TableName,
XLFile, True, XLSheet
Next z

MsgBox "Imported Successfully "

XLapp.Quit
Set XLapp = Nothing
Set XLwb = Nothing

End Function
'*****************************

You can replace in the TransferSpreadsheet 'TableName' by e.g. 'TableName &
z' or by the sheet name.
I have learned recently by a post from John, that you don't need to specify
a range when you want to import/export only a sheet, the only thing what is
required in the '!' after the sheet name, e.g. 'Sheet1!'.

HTH
Bernd