Obtener los nombres de Hojas en otro archivo excel

Inserta un modulo en tu proyecto ( desde el editor de visual basic ALT + F11)
Una vez insertado el modulo pegas este macro:
Sub lista_hojas()
Dim r As Worksheet
Dim m() As String
Dim ca As String
Dim i As Integer
Dim ii As Integer
Dim libro As String
' el presente macro lista el nombre de las pestañas del libro que se ha indicado en celda A1
' el nombre del libro debe contener la ruta completa ej: c:\mi_libro.xls
' version 2007 30/08/2011
libro = UCase(Trim([a1]))
If Len(libro) = 0 Then Exit Sub
Application.ScreenUpdating = False
Workbooks.Open Filename:=libro
libro = Replace(libro, "C:\", "")
libro = Replace(libro, "D:\", "")
For Each r In Sheets
ca = ca & r.Name & "#"
Next
Set r = Nothing
m = Split(ca, "#")
ii = 1
ActiveWindow.Close
Windows("Libro1").Activate
For i = LBound(m) To UBound(m)
Range("B" & ii) = m(i)
ii = (ii + 1)
Next
Erase m
ca = ""
Application.ScreenUpdating = True
End Sub
Grabas el libro y ya.
El macro se llama "lista_hojas"
Condiciones de funcionamiento:
En la celda a1 debes poner la ruta completa del libro + su nombre y extensión
Ej:
c:\mis documentos\carpeta1\libro.xls
Una vez contenido ese dato presionas ALT + F8 y corres el macro
Te devolverá el nombre de las hojas de ese libro en la columna B
No olvides CERRAR la pregunta

Ups1.
Olvide incluir una variable, je, je, je. Ahora si:
Sub lista_hojas()
Dim r As Worksheet
Dim m() As String
Dim ca As String
Dim i As Integer
Dim ii As Integer
Dim libro As String
Dim origen As String
' el presente macro lista el nombre de las pestañas del libro que se ha indicado en celda A1
' el nombre del libro debe contener la ruta completa ej: c:\mi_libro.xls
libro = UCase(Trim([a1]))
If Len(libro) = 0 Then
msgbox "Falta el path en celda A1, ejemplo : c:\mi_libro.xls", vbCritical
Exit Sub
End If
origen = ActiveWorkbook.Name
Application.ScreenUpdating = False
Workbooks.Open Filename:=libro
libro = Replace(libro, "C:\", "")
libro = Replace(libro, "D:\", "")
For Each r In Sheets
ca = ca & r.Name & "#"
DoEvents
Next
Set r = Nothing
m = Split(ca, "#")
ii = 1
ActiveWindow.Close
Windows(origen).Activate
For i = LBound(m) To UBound(m)
Range("B" & ii) = m(i)
ii = (ii + 1)
DoEvents
Next
Erase m
ca = ""
Application.ScreenUpdating = True
End Sub

:D sale bien, pero solo es con 1 archivo :( para los casos que son más de 1 como podría obtenerlos. Tendría que colocar 1 x 1
T_T