SaveAs problems

[Stuart]

User chooses a folder. Code operates on workbooks
in that folder in sequence, first doing some calculations,
then running a protection routine, before moving on to
the next workbook.

I wish to save these files (with changes) back to the
original folder ( and overwrite), and also to a new folder
with a new filename. However, I also need to keep the
file open (with the original filename).

With all files being saved to the "C" drive, and with the
changed files open (with original filenames),

Here's what I have:

'to save with new filename to new folder:
ChDir NextVlnDir
ActiveWorkbook.Protect
ActiveWorkbook.SaveAs FileName:=NameOfFile

'result: correctly saves new filename to new folder
but no protection

Where is my error, please?

Regards.

 

[Rob van Gelder]

I'm guessing that you may actually be after worksheet protection rather than
workbook protection, so I've included lines for protecting all the
worksheets.
The commented lines are for protecting worksheets.

Also, I've commented out the bit about wkb.Close, beause you said it should
remain open.


Sub test()
Const cDir = "C:\T\", cDestDir = "C:\T\df\"
Dim strFile As String, wkb As Workbook
' Dim wks As Worksheet

strFile = Dir(cDir & "*.xls")
Do Until strFile = ""
Set wkb = Workbooks.Open(cDir & strFile)
wkb.Protect
' For Each wks In wkb.Worksheets
' wks.Protect
' Next
wkb.Save
' wkb.Close
FileCopy cDir & strFile, cDestDir & strFile
strFile = Dir
Loop
End Sub

 

[Tom Ogilvy]

What is your expectation of a protected workbook?

Doing a saveas will change the name of the workbook.


pseudocode

for each workbooktobeopened

set wkbk = workbooks.Open(filename:=fname)
wkbk.Protect
' other changes
wkbk.Save
wkbk.Savecopyas "c:\myfolder\ & newname
Next

 

[Stuart]

Many thanks to you both.

Regards

I'm guessing that you may actually be after worksheet protection rather than
workbook protection, so I've included lines for protecting all the
worksheets.
The commented lines are for protecting worksheets.

Also, I've commented out the bit about wkb.Close, beause you said it should
remain open.


Sub test()
Const cDir = "C:\T\", cDestDir = "C:\T\df\"
Dim strFile As String, wkb As Workbook
' Dim wks As Worksheet

strFile = Dir(cDir & "*.xls")
Do Until strFile = ""
Set wkb = Workbooks.Open(cDir & strFile)
wkb.Protect
' For Each wks In wkb.Worksheets
' wks.Protect
' Next
wkb.Save
' wkb.Close
FileCopy cDir & strFile, cDestDir & strFile
strFile = Dir
Loop
End Sub