Create a new file and delete password protection

Question

I use the following VBA to save a new file on my desktop:

Sub Files() ActiveWorkbook.SaveCopyAs "C:\Users\" & Environ("Username") & "\Desktop\testfile.xlsm" Workbooks.Open "C:\Users\" & Environ("Username") & "\Desktop\" & "testfile.xlsm", UpdateLinks:=False MsgBox ("File saved successfully on desktop.") ThisWorkbook.Close SaveChanges = False End Sub

All this works fine so far.

My original file is proteced with a password. This protection should be deleted in the new file which is created using the VBA above.

For unprotecting the file I have the following VBA:

Sub Unprotection() Dim b As Worksheet For Each b In Worksheets b.Unprotect Password:="abc" Next b End Sub

However, i do not know how to enter this code into the procedure of creating the new file. I tried to go with the below code but it only runs in the original file and not in the new file I have created.

Sub Files() ActiveWorkbook.SaveCopyAs "C:\Users\" & Environ("Username") & "\Desktop\testfile.xlsm" Workbooks.Open "C:\Users\" & Environ("Username") & "\Desktop\" & "testfile.xlsm", UpdateLinks:=False Call Unprotection MsgBox ("File saved successfully on desktop.") ThisWorkbook.Close SaveChanges = False End Sub

Do you have any idea how to solve this issue?

Christian Welsch · Accepted Answer · 2018-07-16 11:42:33Z

If you don't want to unprotect your original workbook you can pass the new workbook as a parameter to Unprotection:

Sub Files() Dim wb As Workbook ActiveWorkbook.SaveCopyAs "C:\Users\" & Environ("Username") & "\Desktop\testfile.xlsm" Set wb = Workbooks.Open("C:\Users\" & Environ("Username") & "\Desktop\" & "testfile.xlsm", UpdateLinks:=False) Call Unprotection(wb) MsgBox ("File saved successfully on desktop.") ThisWorkbook.Close SaveChanges = False End Sub Sub Unprotection(wb As Workbook) Dim b As Worksheet For Each b In wb.Worksheets b.Unprotect Password:="abc" Next b End Sub

jamheadart · Accepted Answer · 2018-07-16 11:35:31Z

Quickest fix is to move Unprotection to the very start of your routine. You don't need to reprotect because you close the calling workbook without saving the changes anyway.

Sub Files() Call Unprotection ' unprotect calling workbook sheets ActiveWorkbook.SaveCopyAs "C:\Users\" & Environ("Username") & "\Desktop\testfile.xlsm" ' save a copy with non-protected sheets Workbooks.Open "C:\Users\" & Environ("Username") & "\Desktop\" & "testfile.xlsm", UpdateLinks:=False ' open the new non-protected book MsgBox ("File saved successfully on desktop.") ' Message ThisWorkbook.Close SaveChanges = False ' Close the calling workbook without saving the unprotected sheets End Sub

Thanks for your help. Actually, I do not know why but when I go with SaveChanges = False somehow the original file is still saved without the protection. Only if I re-protect it again it works.
First time I tried it I thought you were right but it's because I forgot to save it with all sheets protected again. 2nd time I tried it, it worked fine.. make sure you have all protections on original file before saving and running!

Collectives™ on Stack Overflow

Create a new file and delete password protection

2 Answers 2

Comments

2 Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Related