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
