This is a very simple yet powerful Excel VBA tip for you. Many a times before sending your workbook with Excel VBA Macro code in it, you want to save your workbook without any VBA code in it.
Therefore here in this article, I will show you – how to save your Excel Workbook without excel macro in it.
This can be done manually as well as using Excel VBA.
At the end of this article, I have a usage example of this VBA method. You can also download a FREE Excel Workbook for you to understand and play-around.
Manual Method: Save Workbook without Excel Macro
Follow the below steps to save a copy of your workbook as Excel Macro FREE. There will be no excel Macro code in the copy saved.
On clicking on save, you receive following confirmation popup, once you click on “Yes” then your excel workbook will be saved without Excel Macro code.
Now once you open your .xlsx workbook, you would not see any of the excel macro code as shown in below picture:
VBA Method: Save Workbook without Excel Macro using Excel VBA
Now as you have seen how to achieve this manually, I will show you how you can achieve this by using Excel VBA.
Following VBA statement will be able to save your Workbook with Excel Workbook as a Workbook without any Excel VBA code in it.
ThisWorkbook.SaveAs Filename:="C:\...\abc.xlsx", FileFormat:=xlOpenXMLWorkbook
While executing the above, VBA statement, you will receive the same confirmation popup as shown above – which you get while saving your .xlsm workbook as .xlsx.
This can be annoying in an automated process. To ignore this popup in Excel VBA code, you simply use Application.DisplayAlerts to false before executing the above statement.
Save Excel workbook as Excel Macro FREE Workbook without confirmation Popup
Refer the below VBA code to save your Excel workbook without saving any VBA code in it.
Application.DisplayAlerts = False ThisWorkbook.SaveAs Filename:="C:\...\abc.xlsx", FileFormat:=xlOpenXMLWorkbook Application.DisplayAlerts = True
Example of usage of above Code
For example, if you want to share your workbook as an attachment, you ca save a copy of your current workbook as Macro FREE.
Send current workbook as Macro FREE Workbook as an Attachment in email
Using below code, you can send your current workbook without any VBA code in it which already has VBA codes in it. This is one of the best usage of saving a workbook as Excel Macro FREE Workbook.
Before sending your workbook as an attachment in email, you do not want to share the code along-with the workbook, then this piece of code will do the magic for you.
Sub Email_CurrentWorkBook() 'Do not forget to change the email ID 'before running this code Dim OlApp As Object Dim NewMail As Object Dim TempFilePath As String Dim fileName As String Dim originalWB As Workbook Dim tempWB As Workbook Dim tempXLSXPath As String Dim tempXLSMPath As String Set originalWB = ThisWorkbook With Application .ScreenUpdating = False .EnableEvents = False .DisplayAlerts = False End With ' Save your workbook in your temp folder of your system ' below code gets the full path of the temporary folder ' in your system TempFilePath = Environ$("temp") & "\" fileName = VBA.Left(originalWB.Name, (InStrRev(originalWB.Name, ".", -1, vbTextCompare) - 1)) fileName = fileName & "-" & Format(Now, "dd-mmm-yy h-mm-ss") tempXLSMPath = (TempFilePath & fileName & ".xlsm") ' with macro - as is tempXLSXPath = (TempFilePath & fileName & ".xlsx") ' without macro ' first an as is copy of this workbook is created ' and saved as with Macro in it [.xlsm] format originalWB.SaveCopyAs (tempXLSMPath) ' Now open this copy of the current workbook ' and saveAs a Macro FREE Workbook [.xlsx] format Set tempWB = Workbooks.Open(tempXLSMPath) With tempWB .SaveAs fileName:=tempXLSXPath, FileFormat:=xlOpenXMLWorkbook .Close savechanges:=False End With ' Now open a new mail Set OlApp = CreateObject("Outlook.Application") Set NewMail = OlApp.CreateItem(0) On Error Resume Next With NewMail .To = "firstname.lastname@example.org" .CC = "email@example.com" .BCC = "firstname.lastname@example.org" .Subject = "Type your Subject here" .Body = "Type the Body of your mail" .Attachments.Add tempXLSXPath ' attach .xlsx file .Send 'or use .Display to show you the email before sending it. End With On Error GoTo 0 ' Since mail has been sent with the attachment ' Now delete both the temp files ' .xlsx and .xlsm Kill tempXLSMPath Kill tempXLSXPath 'set nothing to the objects created Set NewMail = Nothing Set OlApp = Nothing Set originalWB = Nothing Set tempWB = Nothing 'Now set the application properties back to true With Application .ScreenUpdating = True .EnableEvents = True .DisplayAlerts = True End With End Sub
In the above code, as you can see, first I have created an exact copy of the current workbook with Macro and then I have saved that copy as a Macro FREE workbook. Strange?? Isn’t it?? Why did not I directly used SaveAs statement to save the current workbook as Macro FREE workbook and then attach it to the email and send?
There is a valid reason for doing so… Before I explain the reason, I would like you to read these two main differences between these two methods…
Difference between SaveAs and SaveCopyAs in Excel VBA
Following are the two main differences between these two methods in Excel VBA…
Difference No: 1 :
After running the SaveAs command, new workbook becomes the current Workbook [in VBA terms – ThisWorkbook]. In other words, after running the SaveAs command, your original workbook will no longer remain opened and accessible [unless you open the original workbook again by providing the Workbooks.Open command].
While SaveCopyAs does it exactly opposite. Here original workbook remains open and copy is made as-it-is with the given file name and path.
This is the reason… why I could not directly use this SaveAs method to save a macro free copy of the current workbook where actually my code is running. So as soon as Save As command is run, then in the current workbook there is no macro any more and code will stop running there and further statements will not be executed any more.
Difference No: 2 :
Using SaveAs you can change many things around your Excel Workbook.. like
1. Secure it by providing a password
2. Change the FileName, FilePath, FileFormat, FileType etc.
While using SaveCopyAs, all you can do is just change the FileName or FilePath or both. Other than this, you can not make any changes in the workbook. It is simply an AS-IS copy of your workbook.
This is the reason… why I could not just use SaveCopyAs method. By using this method, I can not change for FileFormat of the Workbook as Macro FREE (.xlsx).
Now you understand, why a combination of these two methods was required in order to achieve this scenario of sending a Macro FREE copy of current Excel Workbook as an attachment in an email.
Download your Excel Workbook with Code – Send Macro FREE copy of current Workbook
Did you like reading this article?
Then share it with your colleagues and friends. If you have a suggestion, question etc. please write them in the comment section, and I would come back to you as soon as possible.
You can also read…
You can read more about sending email using Excel VBA here
- How to Send ActiveWorkbook as attachment in Email
- How to Send email with the Active Sheet attached in the mail. (Send One Sheet)
- Send Sheet as a PDF attachment in Email