If you find any post useful then, please do share with others. Thanks!

Popular Posts

Contact

Email me

Need help and that also free? I like to learn this way, in case of any question or for a small task, please feel free to email me with details and example data, if required.

Monday, April 3, 2023

Excel VBA: save a macro-enabled Excel file as a non-macro file

 In this code, you need to replace "C:\Users\UserName\Documents\NewFile.xlsx" with the file path and name where you want to save the new file. The xlOpenXMLWorkbook file format argument specifies that the file should be saved as a non-macro-enabled file.


Before saving the file, the code disables macros using the EnableReferences property of the VBE project. After saving the file, it enables macros again. This is important because if the file is saved with macros enabled, it will still be a macro-enabled file even if the file extension is changed.


Sub SaveAsNonMacroFile()

    'Set the file path and name for the new file

    Dim NewFilePath As String

    NewFilePath = "C:\Users\UserName\Documents\NewFile.xlsx"

    

    'Disable macros

    ThisWorkbook.VBProject.VBE.ActiveVBProject.References.EnableReferences = False

    

    'Save the file as a non-macro-enabled file

    ActiveWorkbook.SaveAs Filename:=NewFilePath, FileFormat:=xlOpenXMLWorkbook

    

    'Enable macros

    ThisWorkbook.VBProject.VBE.ActiveVBProject.References.EnableReferences = True

End Sub


No comments:

Post a Comment