Renombrar Archivos de Subcarpeta con Macro

Para excel v2007

Inserta un modulo en tu proyecto y en el pones esto

Option Explicit

'4c7569735f50

Public nfil As Double
Private Declare Function FindFirstFile Lib "kernel32" Alias "FindFirstFileA" ( _
ByVal lpFileName As String, _
lpFindFileData As WIN32_FIND_DATA) As Long
'Esta el siguiente archivo o directorio
Private Declare Function FindNextFile Lib "kernel32" Alias "FindNextFileA" ( _
ByVal hFindFile As Long, _
lpFindFileData As WIN32_FIND_DATA) As Long
Private Declare Function GetFileAttributes Lib "kernel32" Alias "GetFileAttributesA" ( _
ByVal lpFileName As String) As Long
'Esta cierra el Handle de búsqueda
Private Declare Function FindClose Lib "kernel32" (ByVal hFindFile As Long) As Long
'funciones para conversion de fecha y hora
Private Declare Function FileTimeToLocalFileTime Lib "kernel32" (lpFileTime As fileTime, lpLocalFileTime As fileTime) As Long
Private Declare Function FileTimeToSystemTime Lib "kernel32" (lpFileTime As fileTime, lpSystemTime As SYSTEMTIME) As Long
' Constantes
'------------------------------------------------------------------------------
'Constantes de atributos de archivos
Const FILE_ATTRIBUTE_ARCHIVE = &H20
Const FILE_ATTRIBUTE_DIRECTORY = &H10
Const FILE_ATTRIBUTE_HIDDEN = &H2
Const FILE_ATTRIBUTE_NORMAL = &H80
Const FILE_ATTRIBUTE_READONLY = &H1
Const FILE_ATTRIBUTE_SYSTEM = &H4
Const FILE_ATTRIBUTE_TEMPORARY = &H100
'Otras constantes
Const MAX_PATH = 260
Const MAXDWORD = &HFFFF
Const INVALID_HANDLE_VALUE = -1
'UDT
'------------------------------------------------------------------------------
'Estructura para las fechas de los archivos
Private Type fileTime
dwLowDateTime As Long
dwHighDateTime As Long
End Type
'Estructura necesaria para la información de archivos
Private Type WIN32_FIND_DATA
dwFileAttributes As Long
ftCreationTime As fileTime
ftLastAccessTime As fileTime
ftLastWriteTime As fileTime
nFileSizeHigh As Long
nFileSizeLow As Long
dwReserved0 As Long
dwReserved1 As Long
cFileName As String * MAX_PATH
cAlternate As String * 14
End Type
Private Type SYSTEMTIME
WYear As Integer
WMonth As Integer
WDayOfWeek As Integer
WDay As Integer
WHour As Integer
WMinute As Integer
WSecond As Integer
WMilliseconds As Integer
End Type
Sub buscar()
Dim Path As String
Dim Pattern As String
Dim FileSize As Currency
Dim Count_Archivos As Long
Dim Count_Dir As Long
Path = "C:\Prueba\" ' directorio base
Pattern = "*.jpg" ' extension que debe buscar
'Llamamos a la función para buscar y que nos retorne algunos datos
FileSize = FindFilesAPI(Path, Pattern, _
Count_Archivos, _
Count_Dir)
If Count_Archivos = 0 Then
MsgBox "no se encotraron archivos", vbCritical
Else
MsgBox "Archivos Renombrados", vbInformation
End If
End Sub
Function FindFilesAPI(Path As String, _
SearchStr As String, _
FileCount As Long, _
DirCount As Long)
'Esta función es la principal que permite buscar _
los archivos y listarlos
Dim FileName As String
Dim DirName As String
Dim dirNames() As String
Dim nDir As Long
Dim i As Long
Dim hSearch As Long
Dim WFD As WIN32_FIND_DATA
Dim Cont As Long
Dim nb As Long
Dim LastAccess As Double, Created As Double, Modified As Double
If Right(Path, 1) <> "\" Then Path = Path & "\"
nDir = 0
ReDim dirNames(nDir)
Cont = True
hSearch = FindFirstFile(Path & "*", WFD)
If hSearch <> INVALID_HANDLE_VALUE Then
nb = 1
Do While Cont
On Error Resume Next
DirName = Eliminar_Nulos(WFD.cFileName)
If (DirName <> ".") And (DirName <> "..") Then
If GetFileAttributes(Path & DirName) _
And FILE_ATTRIBUTE_DIRECTORY Then
dirNames(nDir) = DirName
DirCount = DirCount + 1
nDir = nDir + 1
ReDim Preserve dirNames(nDir)
End If
End If
DoEvents
Cont = FindNextFile(hSearch, WFD)
Loop
Cont = FindClose(hSearch)
End If
hSearch = FindFirstFile(Path & SearchStr, WFD)
Cont = True
If hSearch <> INVALID_HANDLE_VALUE Then
While Cont
FileName = Eliminar_Nulos(WFD.cFileName)
If (FileName <> ".") And (FileName <> "..") Then
FindFilesAPI = FindFilesAPI + (WFD.nFileSizeHigh * MAXDWORD) _
+ WFD.nFileSizeLow
FileCount = (FileCount + 1)
nfil = FileCount
Name Path & FileName As Path & "PICTURE.JPG" ' REMPLAZAMOS NOMBRE
End If
Cont = FindNextFile(hSearch, WFD)
Wend
Cont = FindClose(hSearch)
End If
If nDir > 0 Then
For i = 0 To nDir - 1
FindFilesAPI = FindFilesAPI + FindFilesAPI(Path & dirNames(i) & "\", _
SearchStr, FileCount, DirCount)
Next i
End If
End Function
Function Eliminar_Nulos(OriginalStr As String) As String
If (InStr(OriginalStr, Chr(0)) > 0) Then
OriginalStr = Left(OriginalStr, InStr(OriginalStr, Chr(0)) - 1)
End If
Eliminar_Nulos = OriginalStr
End Function

** el macro se llama buscar. antes de ejecutarlo debes cambiar el path y pattern

Path = "C:\Prueba\" ' directorio base
Pattern = "*.jpg" ' extension que debe buscar