Bueno pues al final lo he terminado, me ha costado más ya que no fui capaz de utilizar el Dir anidado que era lo que tenia en mente, así que leí el contenido de la carpeta y lo guarde en un array que luego recorro abriendo los archivos. Tampoco se crear un array con indice variable así que lo dejé fijo a 100 ya que el directorio contenía menos carpetas.
Me ha funcionado perfectamente, dejo el código:
Código: Seleccionar todo
'lista un directorio dado y guarda las carpetas que contiene en un array
'recorremos el array de carpetas y leemos los ficheros que contiene
'se renombran las hojas de cada fichero por el nombre del mismo
Sub DirRenSheet()
'creamos un array con las carpetas a leer
Dim cDir As String
Dim conta As Integer
Dim arrayCarpetas(100) As Variant
Dim path As String
path="D:\OFICIOSP\"
conta=-1
cDir = Dir(path, 16)
Do While cDir>""
Select Case Left(cDir,1)
'Nos saltamos los ocultos
Case ".", ".."
Case Else
conta=conta+1
arrayCarpetas(conta) = cDir
'MsgBox conta & "-" & cDir
End Select
'siguiente directorio
cDir=Dir
Loop
'MsgBox "No quedan más carpetas"
'recorremos el array de carpetas y procesamos los ficheros que contienen
Dim co1 As Integer
'msgbox(LBound(MyArray()))
'msgbox(UBound(MyArray()))
For co1 = 0 To conta
'MsgBox co1 & "-" & arrayCarpetas(co1)
Dim cArchivo As String
Dim cArchivoSinExt As String
Dim carpeta As String
carpeta=arrayCarpetas(co1)+"\"
'MsgBox carpeta
cArchivo = Dir(path+carpeta+"*.ods", 0)
'MsgBox cArchivo
Do While cArchivo>""
'abrimos el archivo
Url = convertToUrl(path+carpeta+cArchivo)
Dim Doc
Doc = starDeskTop.loadComponentFromUrl (Url, "_blank", 0, Array())
'renombramos la hoja por el nombre del archivo
Dim oSheet
oSheet = ThisComponent.CurrentController.ActiveSheet
cArchivoSinExt=Replace(cArchivo,".ods","")
'cArchivoSinExt = join(split(cArchivo, ".ods"), NewPart)
'MsgBox cArchivoSinExt
oSheet.Name = cArchivoSinExt
'guardamos y cerramos el archivo
Doc.store()
Doc.close(true)
'siguiente archivo
cArchivo=Dir
Loop
'MsgBox "No quedan más archivos"
Next
End Sub