Setting PW Before Saving?

P

PeteCresswell

I've got a template workbook that needs tb cloned 75 times.

Each clone will be named for a different person and have it's own
password - as in Tools|Options|Security|Password To Open.

I'm doing ok spawning the sheets by opening up the template and then
doing SaveAs using another worksheet that lists user names and
passwords.

But I'm trapping out when trying to set the password.

"1004: THe password you specified is not correct. Verify that the
caps lock key is off"

The template sheet does have a PW - so I'm guessing that when
setting .Password there's some syntax for specifying the existing PW

When the routine called by line 2050 executes, Excel does prompt me
for the template's PW... and I do supply it.

But it doesn't seem tb letting me reset it in code.

Seems to work ok in an Immediate window though...

??

Code snip:
============================================
' ----------------------------------------------
' Open up the template from which we will spawn all those
' workbooks. Needless to say, it must also reside in the
' same directory as this application

2050 SpreadSheetOpen_Existing myAppPath & "\" & bookName_Template,
myExcelApp
2051 Set myTemplateBook = myExcelApp.Workbooks(bookName_Template)


' ----------------------------------------------
' Loop through the user list, saving a copy of the template
' for each user

2210 k = userRow_Last - userRow_First + 1
2211 SysCmd acSysCmdInitMeter, "Spawning " & k & " workbooks...", k
2219 k = 0

2220 For R = userRow_First To userRow_Last
2230 With myPwSheet
2231 curPW = "xxx" '.Cells(R, 3).Value
2232 curUserName = .Cells(R, 1).Value & "_" & .Cells(R, 2).Value
2233 myTemplateBook.Password = curPW
2234 myTemplateBook.SaveAs myAppPath & "UserBooks\" & curUserName
& ".xls"
2239 End With

2240 k = k + 1
2241 SysCmd acSysCmdUpdateMeter, k
2249 Next R
============================================
 
P

PeteCresswell

Oops! Poked and prodded a little and now it works.

Sorry for the misleading post.
 
Top