Need vba to loop thru all workbooks in a folder to Protect

[DeFacto]

Hi!

Looking for vba code that will loop through all workbooks in a folder/directory and password-protect all the workbooks in that folder. The challenge is that the folder/directory will change from month-to-month, so I need vba code that is flexible enough to run through any given folder.

Any assistance you can offer will be greatly appreciated!

Thanks!

 

[texasalynn]

I just got some code working that does that with some word documents - see this:
http://www.mrexcel.com/board2/viewtopic.php?t=235860

so you would have to change it up some for your use but shouldn't be a major change.

HTH

 

[DeFacto]

Thanks for the reply!

Actually, the code I've been trying has been a little more basic. After opening all the pertinent workbooks, I've been trying to run the following macro:

Sub ProtectWbks()
Dim Book As Workbook
Password = "password"

For Each Book In Workbooks
On Error Resume Next
Sheets("Plan").Protect Password, True, True, True
Application.ActiveWorkbook.Save
Application.ActiveWorkbook.Close
Next
End Sub

However, I can't get the code to run all the way through... it works on most workbooks but then it hangs up and leaves a handful of workbooks open and unaffected. What am I missing?

Thanks again!

 

[tusharm]

Your For loop uses a variable Book to identify each open workbook, one at a time.

However, inside the loop, where do you use that variable? Instead you have an unqualified reference to Sheets() and then you refer to ActiveWorkbook.

What workbook does the unqualified Sheets refer to? Check XL VBA help for the Sheets() property to find out how to make it refer to a particular workbook (Book in your case).

Also, Application.Activeworkbook returns a reference to the active workbook. Instead, you want a reference to Book.

Thanks for the reply!

Actually, the code I've been trying has been a little more basic. After opening all the pertinent workbooks, I've been trying to run the following macro:

Sub ProtectWbks()
Dim Book As Workbook
Password = "password"

For Each Book In Workbooks
On Error Resume Next
Sheets("Plan").Protect Password, True, True, True
Application.ActiveWorkbook.Save
Application.ActiveWorkbook.Close
Next
End Sub

However, I can't get the code to run all the way through... it works on most workbooks but then it hangs up and leaves a handful of workbooks open and unaffected. What am I missing?

Thanks again!