Macro to rename workbook

[kaffal]

Hi all, Everyday I receive 20 workbooks, named as date_xxx_xxx.wk1, where xx represent some random number. Example : 09032009_23_345.wk1.
These files are stored in a folder which contain all the files received for a month.

I need to rename them on a daily basis. I have 3 cell in my worksheet.
Cell A1: C:\Report ( location of the folder)
Cell A2: 09032009 ( a search criteria for file name)
Cell A3: mb ( location , which I can change )

When I run the macro. It will pick up Cell A1 as the folder to work on. Followed by It will search through the folder for files name containing Cell A2. Then It is found, It will pick up the cell value B1 in the current worksheet.
lastly it will rename the files as "Cell A2_CellA3_CellB1.xls" . For example if the cell B1 in the workbook is Car. The file name will be "09032009_mb_Car.xls".

 

[kaffal]

Any Advise ?thanks

 

[kaffal]

Hi all I attached a sample of my code. Can help me to do the code after each workbooks is open? To rename the file as "fcriteria_Tabname.xls"

fpath = Sheets("menu").Range("D3")
fcriteria = Sheets("menu").Range("D4")
TabNam = Range("D2").Value
   
    With Application.FileSearch
        .NewSearch
        .LookIn = fpath
        .FileType = msoFileTypeExcelWorkbooks
        .FileName = fcriteria & "*.xls"
    

            If .Execute > 0 Then 'Workbooks in folder

              For lCount = 1 To .FoundFiles.Count 'Loop through all.
                 'Open Workbook x and Set a Workbook variable to it
                 Set wbResults Workbooks.Open(FileName:=.FoundFiles(lCount), UpdateLinks:=0)


                  TabName = TabNam & Range("A1").Value
                  'Do the File save here. With file name = fcriteria_Tabname.xls                               
              Next lCount
                 
            End If
            
            Sheets("Menu").Select

    End With