CODEWITHSUNDEEP
excelvba
Michi
Michi

Reputation: 5471

Create a new file and delete password protection

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?

Upvotes: 1

Views: 125

Answers (2)

Christian Welsch
Christian Welsch

Reputation: 422

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

Upvotes: 2

jamheadart
jamheadart

Reputation: 5293

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

Upvotes: 0

Related Questions