Rename files with excel macro.

[abhay_547]

Hi All,

I want to come up a macro which will rename any of the files .i.e. file with different extensions present in a directory on the basis of the list present in a excel sheet. For e.g. I have old filename in Column A of sheet1 and in Column B I have new file names. The macro should rename the files with the names present in Column B.

Here is the code which I have so far.

Code to list the files present in a directory. This

Please Login or Register  to view this content.

Code to rename files :

Please Login or Register  to view this content.

The Code to list directory files works fine but the code to rename files doesn't work.

Thanks a lot for your help in advance.

[mrice]

Does the output from the directory listing also contain the full path of the files that you want to rename? If not, this may be the cause of the problem.

[abhay_547]

Hi mrice,

Thanks a lot for your reply, I tried to put the path as well along with the file name but still it shows me a runtime error .i.e. Application-defined or object-defined error and gets stuck on the below code line.

Please Login or Register  to view this content.

Below is the code where I am pulling the entire path but still I am facing the above issue.

Please Login or Register  to view this content.

Thanks a lot for your help in advance.

[Krishnakumar]

Hi,

Replace the variable 'R' with 'i' in RenameFiles routine.

[abhay_547]

Hi Krishna,

Thanks a lot for your reply, I have made the change suggested by you and now it's working fine now. But I still have one issue that I don't want the macro to list the files along with it's entire path in the excel sheet and for this I will have to remove the "ListDir &" text from the line "Cells(R,1).Value = ListDir & Flist" from ListDirectoryFiles Macro.

Can we save the path in the background or it should be reflecting only in one cell for e.g. as of now we will be having the old and new filenames in column A and B. So can we put the path in the Cell C1 .i.e. only once because anyone the all the files which I want to rename are saved in the one directory. Please help...

Please Login or Register  to view this content.

Thanks a lot for your help in advance.

[mrice]

You can remove paths like this.

Please Login or Register  to view this content.

[abhay_547]

Hi mrice,

Thanks a lot for your reply, I tried to incorporate the code posted by you in the main macro but it's not working. Can you please let me know what is going wrong. Here is the modified code.


Code to Rename Files :

Please Login or Register  to view this content.

Code to list out directory files :

Please Login or Register  to view this content.

Thanks a lot for your help in advance.