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".

Or anyone know the code to rename the workbook, with the cell valueB1 for example ?

[davegugg]

This saves the active workbook as the values in the three cells you specified. Put it in a module.

Please Login or Register  to view this content.

It should work, but I'm uncertain because you say the extension currently used is .wk1.

[kaffal]

hi , yr macro works but I still dunno how to apply to my code. I attached a sample of my code.
i want to rename the file as "fcriteria_Tabname.xls" where fcriteria and Tabname are string in the code.

Please Login or Register  to view this content.

[davegugg]

I think you should add .value after your range() when you define your variables, then go:

Please Login or Register  to view this content.

and put that in whatever step you want to save it.

[davegugg]

Maybe like this?:

Please Login or Register  to view this content.

[kaffal]

I added the line after. But it does not rename the workbook

Please Login or Register  to view this content.

[davegugg]

I don't see the line you are talking about. Where did you put it?

[somusas]

hi ,

when i run this macro it says object deos not support .for with application.filesearch , i think is that any reference to be included , please advice

[shg]

Support for the FileSearch method was discontinued in Excel 2007.

[somusas]

will it run in 2003 or 2010

[shg]

Yes and no respectively.

[somusas]

could not get your answer i am sorry , 2010 is yes and 2003 is no , is it?

[shg]

It was discontinued in 2007, so earlier versions work and later versions don't.