Save copy as protected and macro free

[sharky12345]

I'm trying to save a copy of the activeworkbook as an xlsx macro free file, read only and protected but I can't get this to work:

SaveName = Left$(ThisWorkbook.Name, InStrRev(ThisWorkbook.Name, ".") - 1) & " (Read Only).xlsx"


ActiveWorkbook.SaveCopyAs Filename:=ThisWorkbook.Path & "\" & SaveName

 SavedFile = ThisWorkbook.Path & "\" & SaveName

SavedFile.Protect Structure:=True, Windows:=False, Password:=""
 VBA.SetAttr ThisWorkbook.Path & "\" & SaveName, vbNormal

It saves the file, but I'm getting an error 424 Object Required on this line:

SavedFile.Protect Structure:=True, Windows:=False, Password:="password"

Can anyone advise what the problem is?

 

[Fluff]

Is the activeworkbook an xlsx file?

 

[sharky12345]

Cheers Fluff, no, the workbook this is being run from is an xlsm file.

 

[Fluff]

In that case you cannot use Savecopyas, as it doesn't allow you to change the file format.

 

[sharky12345]

Ahh, ok, didn't realise that - is there any way I can save a macro-free version then?

 

[Fluff]

How about

Sub sharky()
   Dim Fname As String
   With ThisWorkbook
      Fname = Left(.FullName, InStrRev(.FullName, ".") - 1)
      Application.DisplayAlerts = False
      .SaveAs Fname, 51, , , True
      Application.DisplayAlerts = True
   End With
   With ActiveWorkbook
      .Protect , True, False
      .Save
   End With
End Sub

 

[sharky12345]

Perfect, thanks..

 

[Fluff]

You're welcome & thanks for the feedback