Saving current xlsm workbook without macros

Kelvin Stott

Active Member
Joined
Oct 26, 2010
Messages
338
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"

VBA Code:
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.
 

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
39,296
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
Why do you want to save it as an xlsx format but with an xlsm extensiion?
 

Kelvin Stott

Active Member
Joined
Oct 26, 2010
Messages
338
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

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
39,296
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
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

Active Member
Joined
Oct 26, 2010
Messages
338
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...
 
Last edited:

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
39,296
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
This uses a separate application instance as you can't have two workbooks with the same name open in the same instance:

VBA Code:
    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
 
Solution

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
39,296
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
Why - what have you got against cleaning up after yourself? :)
 

Forum statistics

Threads
1,175,597
Messages
5,898,372
Members
434,708
Latest member
matthewdrhodes

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top