Assign Password to Modify to Groups of Worksheets Simultaneously

S

Sachssci

I need to assign a "Password to Modify" to a large group of Excel 2003
worksheets simulteneously. (I know how to assign them one at a time, but
that would be too time-consuming.) I can use the same password for all of
them.

Please let me know how to do this.

Thanks!
 
D

Dave Peterson

One way:

Option Explicit
Sub testme()

Dim nextWkbk As Workbook
Dim myFileNames As Variant
Dim fCtr As Long
Dim myPassWordToModify As String

myPassWordToModify = "asdf"

myFileNames = Application.GetOpenFilename _
(FileFilter:="Excel files, *.xls", _
MultiSelect:=True)

If IsArray(myFileNames) Then
Application.ScreenUpdating = False
For fCtr = LBound(myFileNames) To UBound(myFileNames)
Application.StatusBar = "Processing: " & myFileNames(fCtr)
Set nextWkbk = Nothing
On Error Resume Next
Set nextWkbk = Workbooks.Open(Filename:=myFileNames(fCtr))
On Error GoTo 0
If nextWkbk Is Nothing Then
MsgBox "Error opening: " & myFileNames(fCtr)
Else
Application.DisplayAlerts = False
On Error Resume Next
nextWkbk.SaveAs Filename:=nextWkbk.FullName, _
FileFormat:=xlNormal, Password:="", _
WriteResPassword:=myPassWordToModify, _
ReadOnlyRecommended:=False, CreateBackup:=False
If Err.Number <> 0 Then
MsgBox "Error saving: " & myFileNames(fCtr)
Err.Clear
End If
On Error GoTo 0
Application.DisplayAlerts = True
nextWkbk.Close savechanges:=False
End If
Next fCtr
Else
MsgBox "try again later!"
End If

With Application
.ScreenUpdating = True
.StatusBar = False
End With

End Sub

Run this macro and then select your files.

You can ctrl-click to select multiple files and you can click on one and then
shift-click on another and select a group of filenames.

And if you're new to macros, you may want to read David's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm
 
D

Dave Peterson

That was my intent.

But if you have doubts (and you should!), you could copy a few workbooks to a
different folder.

Then try the code against the test copies in that folder.

If it works, woohoo!

If it doesn't, post back with the details.

(I didn't have any trouble in my tests.)

====

I think the only other way would be to do each manually--just open, File|saveAs
and supply the password in that dialog.
 
Top