Make the same change to multiple Excel files

bump a de bump

You have 900 files called D:\Annual.xls?

Hello fred2028:

1)Each file in a directory must have a unique name ... could you please explain what the pattern is for the files .

2)The code you have included does not refer to any specific page in each workbook... so be aware it will only affect the first active sheet in each workbook .

Hi Fred,

Please have a look at this:

Please Login or Register  to view this content.

I hope I didn't omit anything important while I was cleaning the code.

abousetta

Hi Fred,

Glad it works. I just need to another another piece of code for it to loop through subfolders. Will hopefully get back to you in a bit.

abousetta

Originally Posted by abousetta

Hi Fred,

Glad it works. I just need to another another piece of code for it to loop through subfolders. Will hopefully get back to you in a bit.

abousetta

Thanks a lot, I really appreciate you helping. I re-did the macro recording in the post above your previous post.

Just had another question. How do I save this code so that it affects all folders and sub-folders? Do I save it as a .bat file in Windows or something?

Hi Fred,

This updated code should do what you want including looping through subfolders.

Please Login or Register  to view this content.

I've added in some descriptions along the way to help you identify areas where you may want to make future modifications.

On a side note, the macro recorder is an excellent way to record actions like formatting, locking/ unlocking, etc. but also records a lot of unnecessary steps like scrolling or selecting cells. This really slows down your code and for futrue purposes you should learn to clean all this out of your recorded code. As a rule of thumb, you never need to scroll and almost never need to select ranges.

Good luck.

abousetta

Originally Posted by abousetta

Hi Fred,

This updated code should do what you want including looping through subfolders.

Please Login or Register  to view this content.

I've added in some descriptions along the way to help you identify areas where you may want to make future modifications.

On a side note, the macro recorder is an excellent way to record actions like formatting, locking/ unlocking, etc. but also records a lot of unnecessary steps like scrolling or selecting cells. This really slows down your code and for futrue purposes you should learn to clean all this out of your recorded code. As a rule of thumb, you never need to scroll and almost never need to select ranges.

Good luck.

abousetta

Thank you very much abousetta, I will try out the code as soon as I'm done this other project. I'll also look into removing the code for scrolling and selecting. I really appreciate your help!

Originally Posted by abousetta

Hi Fred,

This updated code should do what you want including looping through subfolders.

Please Login or Register  to view this content.

I've added in some descriptions along the way to help you identify areas where you may want to make future modifications.

On a side note, the macro recorder is an excellent way to record actions like formatting, locking/ unlocking, etc. but also records a lot of unnecessary steps like scrolling or selecting cells. This really slows down your code and for futrue purposes you should learn to clean all this out of your recorded code. As a rule of thumb, you never need to scroll and almost never need to select ranges.

Good luck.

abousetta

Hey abousetta, thanks for your help. It works fine, with just 2 minor issues

1) I can unprotect the worksheets without a password ... Is there a way to prompt for a password when trying to unprotect?

2) Cells F1:F5 do not seem to be changing colours for some reason, even though I see it in the code

Hi Fred,

The first question is easy. Just remove this part of the code that passes the password to the excel sheets:

Please Login or Register  to view this content.

Of course now you will have to provide a password each time a file is protected.

Second part, I tried it and it seems to change the font color. Did you want it to change the cell fill in color instead? In other words, if you type something in these cells, you find that the font is Arial, 9 pts, with a light blue font color but the cell itself does not have any shading.

abousetta

P.S. you don't have to quote the whole post. If you need to quote a part of it then that is fine, but quoting the whole post especially if it contains code makes it difficult to find your post.

For the first question I meant after the script is run, I can unprotect the worksheet without using a password even though there is a line that says .Protect "123"

I just want to change the font colour in the cell, not the fill colour. I am using Excel 2010 on Windows XP x86, is that an issue?

Also, how are you supposed to run these scripts? Currently, I am running them inside a macro (Developer tab > Macros) but I don't know if that is the right way.

Thanks!

Hi Fred,

I am a little confused so please bear with me. The macro will open each file (and if the file is password protected then it unprotects it using the password "123") makes the modifications and then password protects the file (using the password "123") before saving and closing the file. If you come to open the file through Excel or by clicking on it after the code has run, it should ask for a password. This is what happens on my computer (Windows 7, Office 2010) and should happen the same way on your system as well.

As for the second part, the font color is changes. If you write something in the cells then it should appear a shade of blue. Is something different happening on your system. If so, please upload a copy of one of the files that has already been modified by the macro.

Finally, there are many ways to run a macro but the most common three ways off the top of my head are (1) the way you described (or Alt+F8), putting a button (or other shape object) in the excel sheet and clicking on it, or by assigning a combination of Ctrl+Alt+ a letter. They will all acomplish the same task.

abousetta

Originally Posted by abousetta

Hi Fred,

I am a little confused so please bear with me. The macro will open each file (and if the file is password protected then it unprotects it using the password "123") makes the modifications and then password protects the file (using the password "123") before saving and closing the file. If you come to open the file through Excel or by clicking on it after the code has run, it should ask for a password. This is what happens on my computer (Windows 7, Office 2010) and should happen the same way on your system as well.

Hmmm I will try this on my Windows 7 laptop at home today and see what happens.

As for the second part, the font color is changes. If you write something in the cells then it should appear a shade of blue. Is something different happening on your system. If so, please upload a copy of one of the files that has already been modified by the macro.

On my system, the font colour in cells F1:F5 remain black. There is already text in the cells, and their colours have not changed. I have attached a file with the macro in it. The cells, F1:F5, are black.

Finally, there are many ways to run a macro but the most common three ways off the top of my head are (1) the way you described (or Alt+F8), putting a button (or other shape object) in the excel sheet and clicking on it, or by assigning a combination of Ctrl+Alt+ a letter. They will all acomplish the same task.

abousetta

OK, so just to be clear, running this macro the way I described will modify each file named annual.xls in this folder and its subfolders even though it's running from within an Excel file?

OK I just tried again, and the file annual.xls is protected after I run the code, however if I close then reopen the file, I can edit all cells (even though it is Protected). I can unprotect the sheets with no password ...

The other macros in your sheet are unprotecting. I will track it down at lunch and give you my advice on how to resolve this.

abousetta

Originally Posted by abousetta

The other macros in your sheet are unprotecting. I will track it down at lunch and give you my advice on how to resolve this.

abousetta

Thank you very much!

Hi Fred,

OK, so after looking at your code, it seems that you have 34 instances of unprotecting and protecting. I have globally added the password "123" to all of them. Therefore when the code runs into one of the unprotect arguments then it will unprotect using the password "123" and same thing for the password argument.

This should resolve the issue with the protection.

Are you still having problems with the font color?

abousetta

Thanks I looked through the other macros and it seems to be the case. However, I am unclear as to how the other macros can unprotect the worksheet without the password even though the macro we wrote protected it with the password "123". Could you shed some light on this?

Also, is it possible for me to add the password "123" to each macro in each worksheet recursively, in the same fashion I am doing now for the formatting changes? Thanks.

After the aforementioned problems, I'm wondering if it's possible to do the changes to the Excel files via a .NET Windows application? If so, what are some topics I should read into, for things like opening Excel files, manipulating them, etc.?

Hi Fred,

I really don't know why its allowing it to unprotect without a password. It is interesting because I thought it needed a password to unprotect a worksheet, even via vba but I guess I was wrong. Maybe the more knowledgeable folks here can shed some light on this subject.

abousetta