Replace and overwrite excel file without prompt

[Harshil Mehta]

Requirements before closing the file:

• Protect all sheets
• Hide all sheets except sheet name "Permissions"
• Replace the file without prompt along with password to open the file (Same file name, Same file path)

The below code does not work as required. Not sure where I am going wrong.

Private Sub Workbook_BeforeClose(Cancel As Boolean)

Dim a110w As Variant
Dim path As String

a110w = "123" 'P
path = Application.ThisWorkbook.path


Application.DisplayAlerts = False

      
      ThisWorkbook.Unprotect Password:=a110w 
      
      For Each Worksheet In Sheets
      
          Worksheet.Protect Password:=a110w
      Next

     For Each Worksheet In Sheets
      
         If Not Worksheet.Name = "Permissions" Then Worksheet.Visible = xlVeryHidden
      Next
      
      

ThisWorkbook.SaveAs Filename:=path, FileFormat:=50, Password:=a110w
      
      
      
Application.DisplayAlerts = True

      

End Sub

 

[Victor Moss]

Use Private Sub Workbook_Open() if you'd like to keep sheets hidden

Private Sub Workbook_Open()

For Each ws In Sheets
If ws.Name <> "Permissions" Then
ws.Visible = False
End If
Next
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)

Dim ws As Worksheet
Dim a110w As Variant
Dim path As String
Dim fName As String

a110w = "123"
path = Application.ThisWorkbook.path
fName = ThisWorkbook.Name

For Each ws In Sheets
ws.Protect
If ws.Name <> "Permissions" Then
ws.Visible = False
End If
Next

Application.DisplayAlerts = False
ThisWorkbook.SaveAs Filename:=path & fName, FileFormat:=52, Password:=a110w
ActiveWorkbook.Close savechanges:=True
Application.DisplayAlerts = True

End Sub

 

[Harshil Mehta]

Use Private Sub Workbook_Open() if you'd like to keep sheets hidden

Private Sub Workbook_Open()

For Each ws In Sheets
If ws.Name <> "Permissions" Then
ws.Visible = False
End If
Next
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)

Dim ws As Worksheet
Dim a110w As Variant
Dim path As String
Dim fName As String

a110w = "123"
path = Application.ThisWorkbook.path
fName = ThisWorkbook.Name

For Each ws In Sheets
ws.Protect
If ws.Name <> "Permissions" Then
ws.Visible = False
End If
Next

Application.DisplayAlerts = False
ThisWorkbook.SaveAs Filename:=path & fName, FileFormat:=52, Password:=a110w
ActiveWorkbook.Close savechanges:=True
Application.DisplayAlerts = True

End Sub

Shows an error saying to change the file extension.
I am already using binary format and want the save as too be in binary format only. Apologies, I did not mention it before.

 

[Victor Moss]

Use FileFormat:=50

 

[Harshil Mehta]

Use FileFormat:=50

Not working for me.

 

[Victor Moss]

I had the same error this morning, I saved the file AS xlsb first, then closed, reopened, when closing again it worked fine.