Hi Kostas,
Should be
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel8, "Employees", "c:\Temp.xls", True, "Sheet3!A1:ZZ999"
Depending on the version of your Excel file (97 until 2002 = 8) you will have to change the acSpreadsheetTypeExcel8 (= default value, not necessary to enter) to acSpreadsheetTypeExcel3, 4, 5, 7 or 9.
True : if your Excel File contains column headings set it to True (or -1), else False (or 0)
"Sheet3!A1:ZZ999" : Sheet name (presuming it's "Sheet3") and range on sheet to import. If you specify a sheet you have to add a range on the sheet.
If you want to find out the 3rd sheet in your file you will need some more VBA: ( have a look at Access help - GetObject and CreateObject)
'************************************
Private Sub 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 Sub
'*****************************
Sorry, took a little bit longer to answer than planned.
