Saving current xlsm workbook without macros

[Kelvin Stott]

Hello,

In order to protect my VBA code, I am trying to get my xlsm workbook to save itself without macros, but under the same xlsm filename...

I have tried the following code, and many variants, but I get a run-time error '1004': "Method 'SaveAs' of object '_Workbook' failed"

Application.DisplayAlerts = False
    ThisWorkbook.SaveAs Filename:=ThisWorkbook.FullName, FileFormat:=xlOpenXMLWorkbook
Application.DisplayAlerts = True

So how do I get an xlsm workbook to save itself under the same xlsm filename, but without any macros?

Any help would be much appreciated, thanks.

 

[RoryA]

Why do you want to save it as an xlsx format but with an xlsm extensiion?

 

[Kelvin Stott]

Why do you want to save it as an xlsx format but with an xlsm extensiion?

Other macros will be looking for the original filename.xlsm. I don't mind saving in proper xlsm format, I just want to remove the existing macros.

 

[RoryA]

I'm not sure there's a simple method for that. Saving in xlsx format doesn't actually remove the macros until the workbook is closed, so you can't save as xlsx then immediately resave as xlsm, and you can't save it with an xlsm extension in xlsx format (even if you could, it would cause errors when you try to reopen the file).

Do you need to remove worksheet code, or just normal modules?

 

[Kelvin Stott]

Just need to remove all normal modules, including the one containing any code that removes the modules!

And yes, the workbook should close immediately after saving itself without macros.

Perhaps there is a way to save first as a temporary xlsx file without macros, then open this and resave as original xlsm filename, then delete the temp xlsx?

Though I feel there should be a much simpler, more direct way to remove macros, without creating a temporary xlsx file...

 

[RoryA]

This uses a separate application instance as you can't have two workbooks with the same name open in the same instance:

    With ThisWorkbook
        Dim currentFile As String
        currentFile = .FullName
        Dim noCodeFile As String
        noCodeFile = Replace$(currentFile, ".xlsm", ".xlsx")
        Dim cleanFile As String
        cleanFile = .Path & Application.PathSeparator & "temp" & .Name
        .SaveCopyAs Filename:=cleanFile
        .ChangeFileAccess xlReadOnly
    End With
    With CreateObject("Excel.Application")
       .DisplayAlerts = False
        Dim wbNewCopy As Workbook
        Set wbNewCopy = .Workbooks.Open(cleanFile)
        wbNewCopy.SaveAs noCodeFile, xlOpenXMLWorkbook
        wbNewCopy.Close False
        Set wbNewCopy = .Workbooks.Open(noCodeFile)
        Kill currentFile
        wbNewCopy.SaveAs currentFile, xlOpenXMLWorkbookMacroEnabled
        Kill noCodeFile
        wbNewCopy.Close False
        .Quit
    End With
    ThisWorkbook.Close

 

[Kelvin Stott]

Nice! But any way to do this without "Killing" anything?

 

[RoryA]

Why - what have you got against cleaning up after yourself?