Importar varias hojas de Excel a Access.

Voy a probar tu código. Mientras una pregunta: ¿Los datos en las hojas tienen formato de base de datos?
Es decir una fila con nombres y luego los datos de debajo sin dejar filas en blanco.
De no ser así contame un poco cómo esperarías que queden en la tabla.

Hasta acá logré importar un libro con una sola hoja. Hice pocos cambios:
1) Agregué la declaración Dim canHojas As Integer (yo tengo activo Option explicit que obliga la declaración de variables - se cambia en Opciones)
2) Obviamente la ruta de acceso.
3) Y esto es importante: en la línea que tenías con comentarios cambié el rango que decía "lista(i)" por el valor del rango de mi ejemplo.
Me importó bien la tabla.
Un consejo: cuando desarrolles una solución andá paso a paso, primero probá que importe bien una, luego varias, luego que detecte el rango a copiar. Trabajar paso a paso es un método profesional que te permite ir resolviendo factores de a uno, de no ser así te enfrentás con errores que a lo mejor son acumulación de varios errorcitos y te confunden y te complican y finalmente te desalientan. El otro método hace que vayas logrando pequeños éxitos y eso ayuda. Al final se llega más rápido a la solución total.
Una pregunta: ¿Los rangos a importar siempre comienzan en A1? Esto simplificaría la autodetección, de lo contrario hay que hacer algo para activar alguna celda dentro dl rango a importar.
Function ContarHoja()
  Dim RutaArchivo As String
  Dim ObjHoja As Object
  Dim strfilename As String
  Dim objexcel As Object
  Dim i As Integer
  Dim objHojas As Object 'Integer
  Dim respuesta As String
  Dim itm As Variant
  Dim Sheetcount As Integer
  Dim canHojas As Integer
  Dim lista()
   strfilename = "C:\Provisorios\Apuntes\Todo Expertos\ImportarExcel.xls"
     If Len(Dir(strfilename)) = 0 Then
       respuesta = MsgBox("El archivo XLS que especificó no existe. Verifique la ruta", vbYesNo + vbCritical + vbDefaultButton2, " Archivo ")
       Exit Function
     End If
   Set objexcel = CreateObject("excel.application")
   With objexcel
        .displayalerts = False
        .workbooks.Open (strfilename)
   Sheetcount = .worksheets.Count
    ReDim lista(1 To Sheetcount)
    MsgBox Sheetcount
   For i = 1 To Sheetcount '.worksheets.Count
     Set objHojas = .sheets(i)
     canHojas = canHojas + 1
     lista(i) = .sheets(i).Name
    DoCmd. TransferSpreadsheet acImport, acSpreadsheetTypeExcel8, "importa", strfilename, True, "A1:C11"
   Next i
     End With
     Set objexcel = Nothing 'close
     Set ObjHoja = Nothing
End Function
Sigo con lo tuyo y espero comentarios. No cierres la consulta todavía, si no yo no puedo agregar nada.
Suerte, M

Ya lo probé con 2 hojas y funciona bien.
Agregué una cosa, cambié otra y eliminé otra
1) Eliminé el msgbox canHojas que había puesto para comprobar que tomaba bien la cantidad de hojas
2) Cambié "importa" por "importa" & i entonces te va a dejar tantas tablas como hojas (después las juntamos)
3) Agregué
     Objexcel. Quit
Antes de
     Set objexcel = Nothing 'close 
Para que cierre el libro.
Finalmente que daría por resolver el rango de cada libro, acá espero tu respuesta y luego con una consulta de agregado de datos podes juntar todas las tablas en una y con consulta de eliminación eliminás las tablas que sobran .
Ahora sí espero tus comentarios, M

Gracias Marciana:
                           Por tu pronta respuesta.
Con respecto a estas consultas :
Pregunta realizada por ti.
    - Mientras una pregunta: ¿Los datos en las hojas tienen formato de base de datos?
    Si, Un titulo de rotulo por Ej. Nombre, Dirección, Etc..., y los datos hacia abajo.
    - Es decir una fila con nombres y luego los datos de debajo sin dejar filas en blanco.
    Correcto
     - De no ser así contame un poco cómo esperarías que queden en la tabla.
Tú, me das la posibilidad de que queden en varias tablas, es posible que importe a una sola tabla. Porque, te puedes imaginar que el modulo no estará visible al Usuario, para evitar que modifiquen algo. Y yo no se cuantas hojas puedan ser.
     - Finalmente que daría por resolver el rango de cada libro, acá espero tu respuesta
El rango de los datos bueno empiezan A2 y terminan p49528, que es hasta donde necesito importar. Porque sigue hasta la columna Z que son datos numéricos que no necesito.
   - Y luego con una consulta de agregado de datos podes juntar todas las tablas en una y con consulta de eliminación eliminás las tablas que sobran .
Esto es para mi imposible de dimensionar la cantidad de hojas que puedan ser, por lo tanto te pido que imagines alguna forma de importar las hojas una en una, y agregándolas a la misma tabla.
Se me olvidaba, si hice lo que me dices probaba el código paso a paso con F8, pero llegado un momento necesite de tu valiosa ayuda, que mejor que preguntar a un Experto. Gracias, hasta el cansancio, Marciana.
Tu respuesta ha sido muy motivante. Gracias.
Atentamente, Nickuser8.

Para que quede todo en una tabla:
Recién probé y funciona bien volver a dejar el nombre de tabla como "importa" (eliminá el"& i")
Si el rango siempre es el mismo es muy fácil
cambiá donde dice
"A1:C11" 
Por
"A1:P49528"
Pero cuidado yo hice una prueba incluyendo renglones en blanco y se colgó.
Estoy probando la sintaxis para detectar la última fila ocupada

Marciana :
                 Muchas gracias, es posible que me envíes el código por correo o me respondas por este mismo conducto. Como gustes.
De nuevo muchas gracias.
Atentamente.

El código carga dos veces la hoja 1. No estoy segura pero creo que en las primeras pruebas cargaba correctamente una y luego otra.
¿Podrías comprobar esto?
Te sugiero que hagas las pruebas con pocos datos, es más ágil.

Marciana :
            Acabo de probar con 3 hojas y me lo hizo tres veces. Pero sólo con la 1era Hoja, en la misma tabla. Me parece muy extraño.
Gracias. Por tu amabilidad y pronta respuesta.
Atentamente. Nickuser8.