How to change names of all workbooks in a folder

[jkindhappy]

Hi,

I have some 150 workbooks in a folder. The name of each work book is some thing like this."XXXXXX June2011". Every month I have to change the names of all workbooks with current month's name.

Now can some one suggest me an easy method to change the names of all workbooks without changing the name of each workbook one by one or some macro to do this.

 

[tigeravatar]

jkindhappy,

Something like this?

Sub tgr()
    
    'Change to desired, make sure to include the ending \
    Const sPath As String = "C:\Test Folder\"
    
    Dim CurrentFile As String: CurrentFile = Dir(sPath & "*.xls*")
    
    Dim wb As Workbook
    Dim OldDate As String
    
    Application.ScreenUpdating = False
    While CurrentFile <> vbNullString
        Set wb = Workbooks.Open(sPath & CurrentFile)
        OldDate = StrReverse(Split(StrReverse(CurrentFile), " ")(0))
        
        wb.SaveAs sPath & Replace(CurrentFile, OldDate, Format(Date, "mmmmyyyy"))
        wb.Close False
        
        Kill sPath & CurrentFile
        CurrentFile = Dir
    Wend
    Application.ScreenUpdating = True
    
End Sub

Hope that helps,
~tigeravatar