Bulk rename excel files based on values of two different cells

Hello everybody

I need your help in this small problem. I tried to use/modify many scripts I found on the internet but none of them does the job.

I have about 500 xlsx files in one single directory (it is a network path), all saved with Excel 2010.

I would like to have a bulk rename of all of them using their values in cell C3 and cell C10 placed in Sheet1

For example... if one of the 500 files contains the text "Paul Smith" in C3 and "Kyle White" in cell C10, then the file will be renamed in "Paul Smith x Kyle White.xlsx"
And so on with all the other xlsx files in that directory
I do not need and user input interface.. just run the macro and voila'... all files renamed with their C3 and C10 values.

The operation should be made using a script placed in a macro enabled excel file that is saved in that directory.

This will save me a lot of time !

Thanks in advance for your kind help

You can link closed workbook via formula, so we can use vba to extract the data from C3 and C10.
So the code will loop through each workbook and enter the formula in your code workbook

Formula:

Please Login or Register  to view this content.

It will then rename the workbook according to the formula value.
Here is the complete code.

Please Login or Register  to view this content.

See attched.

Be sure to edit the code to your folder addresses.

Many thanks for your fast reply.

I modified the path name on your file and run the script.

After the first file is checked and its name is written in the code workbook, I get a runtime error 100 Application-Defined or object-defined error and the debugger highlights me the following line:

a1 = "=[" & MyFile & "]Sheet1!R3C3&""-x-""&[" & MyFile & "]Sheet1!R10C3"

Surely I did something wrong, but I cannot figure out where

P.S: I would like to attach one of those files as an example but the Forum is a bit tricky and I cannot

Attaching a sample workbook enables others to work on your problem:

To attach a sample workbook. Make sure there is just enough data to make it clear what is needed. Include BEFORE/AFTER sheets if needed to show the process you're trying to complete or automate. Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

Did it work the 1st time?

Jees, I was talking about the code.

Anyway the code worked for me even in your attached example.

double post due to forum timeout

You didn't answer the question, did it run the first time?

When you hover over A1 in the code what does it say?

If I run it the first time it works, then run it a second time it fails, I need to rename the sheets again, before using the formula code.

I would click reverse button to, rename the sheets, then click the rename button, to rename as the sheets in the cells you referred to.

See how the attached works for you.

I'll have to Check in later,

But maybe look at this for now.

Please Login or Register  to view this content.

Thanks for your quick reply.
The script now does the job correctly. All the files are renamed according to the value of the cells and, if we have a duplicate, it is not a problem (I tried it also with 4 files with same contents, and the scripts rename everything 1,2,3...). Even if I run it once more in the same folder, with the files just renamed by the script, it does the job again.
So, for me the script is ok but... when it ends, it gives me the same exact error it was giving me on the first try (Run Time Error 1004). It is not a real problem, since the code works, but the last error is a bit annoying.
Please tell me if I have to sign this thread as solved or not.
Thanks again!

P.S. I forgot to add one more thing: since the names in cell C3 and C10 can be both uppercase and lowercase, is it possible to convert everything to uppercase while renaming the files?

I guess we can put a resume next if there is an error, at least it won't go to the degugger then

Please Login or Register  to view this content.