Here is code that I use to import multiple spreadsheet tabs. Note that this does not use any argument for named ranges, but if you look at the link at the bottom, it will tell you how todo that.
Code:
Private Sub Command1_Click()
'---------------------------------------------------------------------------------------
' Procedure : ImportXLSheetsAsTables
' Author : jed
' Date : 1/16/2009
' Purpose : To import all worksheets in a specified workbook into
' individual tables in Access.
' Tables get names: Tbl_ + name of the worksheet
'
' NOTE: Must have a reference to the Microsoft Excel Object Library
' Make sure that the file GEMCAP.xls is located on your desktop. You may have to change the path
' if you are using a newer version of Windows. This was designed to run on Windows XP.
'---------------------------------------------------------------------------------------
'
Dim appExcel As Excel.Application
Dim wb As Excel.Workbook
Dim sh As Excel.Worksheet
Dim strValue As String
On Error GoTo ImportXLSheetsAsTables_Error
Set appExcel = CreateObject("Excel.Application")
Set wb = appExcel.Workbooks.Open("C:\Documents and Settings\" & Environ("UserName") & "\Desktop\GEMCAP.xls")
For Each sh In wb.Sheets
Debug.Print sh.Name
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "tbl_" & sh.Name, "C:\Documents and Settings\" & Environ("UserName") & "\Desktop\GEMCAP.xls", True, sh.Name & "!"
Next
wb.Close
appExcel.Quit
On Error GoTo 0
Exit Sub
ImportXLSheetsAsTables_Error:
MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure ImportXLSheetsAsTables of Module Module9"
End Sub
Look here for import instructions on named ranges.
http://msdn.microsoft.com/en-us/libr...ffice.12).aspx