Rename the Excel Files using VBA Code

[kushaldwivedi]

Hi,

I have more than 1000 excel files in a single folder and there are many folders like this.

I need to write the code through which I can rename all the excel files of a Singhle folder can be renamed with the text present at "H2" in each excel file.

Pls help me regarding the same.


Regards,
Kushal Dwivedi

 

[Worf]

Hi

See if this example helps:

Sub Kushal()
Dim ext$, dn$
' assumes new name always on Sheet1, without a file extension
' does not open the files
dn = "c:\pub"                                   ' the folder
ChDir dn
ext = Dir("*.xls?")
Do While ext <> ""
    Range("c21").FormulaR1C1 = _
    "=if('" & dn & "\[" & ext & "]Sheet1'!r2c8="""",na(),'" & dn & "\[" & ext & "]Sheet1'!r2c8)"
    If Not IsError(Range("c21")) Then _
    Name dn & "\" & ext As dn & "\" & Range("c21") & "." & Split(ext, ".")(1)
    ext = Dir
Loop
End Sub