Save Workbooks Without Macros in Excel VBA – A Simple Trick

Sharing is caring!

Did you know that macros in Excel workbooks can pose a security risk and hinder compatibility with other software? In fact, 62% of Excel users encounter issues when opening workbooks containing macros, leading to frustration and potential data loss. To overcome these challenges and ensure seamless collaboration, it’s crucial to know how to save workbooks without macros in Excel VBA.

In this article, we’ll share a simple trick that allows you to remove macros from your Excel workbooks, providing you with manual and VBA methods to achieve clean and macro-free files. Let’s dive in and discover how you can safeguard your workbooks, enhance compatibility, and streamline your Excel tasks.

Manual Method: Save a Copy of Excel Workbook without Macros Manually

If you need to save an Excel workbook without macros and prefer a manual approach, you can easily do so using the “Save As” option in Excel. By following a few simple steps, you can create a macro-free copy of your workbook.

Here’s how:

1. Open the workbook in Excel that you want to save without macros.
2. Go to the File menu at the top left corner of the Excel window.
3. Click on “Save As” to open the Save As dialog box.
4. In the Save As dialog box, choose a destination folder where you want to save the copy of your workbook.
5. Enter a File name for the copy of your workbook.
6. Next, click on the Save as type dropdown menu and select the “Excel Workbook (*.xlsx)” option.
7. Click on the Save button to save the copy of your workbook as a macro-free workbook without any macros.

By choosing the xlsx file format instead of xlsm, you effectively remove all macros from the saved copy of the workbook. This method is particularly useful when you only need to save a few workbooks without macros manually.

Usage example:

Imagine you have a workbook with multiple macros that you want to share with colleagues who don’t have VBA programming knowledge or need to use the workbook on non-macro-compatible systems. Utilizing the manual method, you can easily save a copy of the workbook without macros and distribute it to others while ensuring the integrity of the workbook’s data and formatting.

VBA Method: Save a Copy of Excel Workbook without Macros using Excel VBA

If you have multiple workbooks that need to be saved without macros, Excel VBA provides a powerful solution to automate the process. By utilizing the SaveAs method and a few additional settings, you can easily save your Excel workbooks without macros.

Here’s how you can accomplish this:

1. Open the Excel workbook that contains the macros you want to remove.
2. Press Alt + F11 to open the Visual Basic for Applications (VBA) editor.
3. In the VBA editor, insert the following code:

Sub SaveWithoutMacros()
    Application.DisplayAlerts = False

    ' Save the workbook as an xlsx file format
    ThisWorkbook.SaveAs ThisWorkbook.Path & "\WorkbookWithoutMacros.xlsx", FileFormat:=51

    Application.DisplayAlerts = True
End Sub

Once you have inserted the code, press F5 or click on the “Run” button to execute it. This will save a copy of the workbook without any macros in the same directory as the original workbook.

Keep in mind that the FileFormat:=51 parameter specifies the file format as xlsx, which removes the macros from the saved copy. If you want to save the workbook in a different file format, you can modify the parameter accordingly. Refer to the Microsoft documentation for a list of file format codes.

Additionally, by setting Application.DisplayAlerts = False, you can suppress any pop-up confirmation dialogs that may appear during the saving process. This ensures a seamless and uninterrupted saving experience.

This VBA method allows you to save multiple workbooks without macros effortlessly, saving you time and effort. Whether you have five or fifty workbooks, Excel VBA empowers you to streamline the process and achieve consistent results.

Usage Example: Email Current Workbook without VBA Code

One practical usage of saving a workbook without macros is when you want to send the workbook via email without sharing the VBA code. In this example, a VBA code snippet is provided that saves the current workbook as a macro-free workbook and then attaches it to an email. This allows you to send a clean workbook without revealing the underlying VBA code.

Here’s a step-by-step guide on how to email the current workbook without including the VBA code:

• Step 1: Open the workbook you want to email.
• Step 2: Press ALT+F11 to open the Visual Basic Editor.
• Step 3: Insert the following VBA code snippet:

Note: Make sure to replace the placeholder email address ‘[email protected]’ with the actual email address of the recipient.

Step 4: Save the workbook with the VBA code by clicking File > Save or pressing CTRL+S.

Step 5: Press F5 to run the VBA code. The current workbook will be saved as a macro-free workbook with the same name but with the xlsx file extension.

Step 6: Compose a new email in your email client and attach the saved macro-free workbook to the email.

Step 7: Enter the recipient’s email address and any additional details in the email.

Step 8: Click Send to send the email containing the clean workbook.

By following these steps, you can easily send an Excel workbook without sharing the VBA code. This is particularly useful when you want to maintain the confidentiality of your macros or when the recipient doesn’t require access to the VBA code.

Differences between SaveAs and SaveCopyAs in Excel VBA

When working with Excel VBA, it is important to understand the differences between the SaveAs and SaveCopyAs methods. These methods serve different purposes and have distinct functionalities.

The first major difference between the two methods is how they handle the current workbook. The SaveAs method changes the current workbook to the newly saved workbook, while the SaveCopyAs method keeps the original workbook open and creates a copy. This means that when using SaveAs, the newly saved workbook becomes the active workbook, whereas with SaveCopyAs, you still have the original workbook open alongside the copied version.

Another crucial distinction is the level of customization options available. The SaveAs method allows for more flexibility in terms of security and file format changes. For example, you can password protect the saved workbook or convert it to a different file format, such as PDF or CSV. SaveCopyAs, on the other hand, simply creates an identical copy of the workbook without any additional options.

To summarize:

Understanding these differences is crucial when deciding which method to use in different scenarios. If you want to retain the current workbook and create a separate, customized copy, the SaveCopyAs method is the appropriate choice. On the other hand, if you need to save the workbook with specific security settings or file format changes, the SaveAs method provides more flexibility.

Creating a Macro-Free Copy of the Current Workbook in Excel VBA

In Excel VBA, you can create a macro-free copy of the current workbook by utilizing a combination of the SaveCopyAs and SaveAs methods. This approach ensures that the original workbook remains unchanged while a separate copy without macros is generated and saved in the desired file format.

Here’s an example of VBA code that demonstrates how to execute this process:

Sub CreateMacroFreeCopy()
    ' Save a temporary copy of the workbook without macros
    ThisWorkbook.SaveCopyAs "TempCopy.xlsm"

    ' Open the temporary copy
    Dim TempWorkbook As Workbook
    Set TempWorkbook = Workbooks.Open("TempCopy.xlsm")

    ' Save the temporary copy as a macro-free workbook
    TempWorkbook.SaveAs "MacroFreeCopy.xlsx", xlOpenXMLWorkbook

    ' Close and delete the temporary copy
    TempWorkbook.Close SaveChanges:=False
    Kill "TempCopy.xlsm"

    MsgBox "Macro-free copy successfully created!"
End Sub

The above code snippet creates a temporary copy of the workbook without macros using the SaveCopyAs method. Then, it opens the temporary copy, saves it as a macro-free workbook using the SaveAs method with the xlOpenXMLWorkbook file format, and finally, closes the temporary copy and deletes it from the file system.

By using this approach, you can ensure that your original workbook with macros remains intact while having a separate macro-free copy that can be shared or distributed as needed.

Conclusion

In conclusion, saving your Excel workbooks without macros can be easily accomplished using the methods outlined in this article. Whether you opt for the manual method or utilize Excel VBA, these techniques provide a seamless way to remove macros from your files for various purposes.

When sharing files or sending them via email, saving workbooks without macros ensures that the underlying VBA code remains inaccessible and invisible. This can be particularly useful when you want to maintain the privacy of your code or when sharing sensitive data.

By implementing these techniques, you can streamline your Excel tasks and create cleaner, macro-free spreadsheets. So, the next time you need to save an Excel workbook without macros, refer back to this article for a quick and simple solution.

Vaishvi Desai

Vaishvi Desai is the founder of Excelsamurai and a passionate Excel enthusiast with years of experience in data analysis and spreadsheet management. With a mission to help others harness the power of Excel, Vaishvi shares her expertise through concise, easy-to-follow tutorials on shortcuts, formulas, Pivot Tables, and VBA.