{"id":14905,"date":"2018-07-04T14:27:21","date_gmt":"2018-07-04T14:27:21","guid":{"rendered":"http:\/\/learnexcelmacro.com\/wp\/?p=14905"},"modified":"2022-08-17T19:16:47","modified_gmt":"2022-08-17T19:16:47","slug":"vba-to-save-macro-free-copy-of-a-workbook","status":"publish","type":"post","link":"https:\/\/vmlogger.com\/excel\/2018\/07\/vba-to-save-macro-free-copy-of-a-workbook\/","title":{"rendered":"Methods to save a Macro FREE copy of a workbook [FREE DOWNLOAD]"},"content":{"rendered":"

[et_pb_section fb_built=”1″ admin_label=”section” _builder_version=”4.16″ da_disable_devices=”off|off|off” global_colors_info=”{}” da_is_popup=”off” da_exit_intent=”off” da_has_close=”on” da_alt_close=”off” da_dark_close=”off” da_not_modal=”on” da_is_singular=”off” da_with_loader=”off” da_has_shadow=”on”][et_pb_row admin_label=”row” _builder_version=”4.16″ background_size=”initial” background_position=”top_left” background_repeat=”repeat” global_colors_info=”{}”][et_pb_column type=”4_4″ _builder_version=”4.16″ custom_padding=”|||” global_colors_info=”{}” custom_padding__hover=”|||”][et_pb_text _builder_version=”4.17.6″ _module_preset=”default” global_colors_info=”{}”]Dear Friends,<\/p>\n

This is a very simple yet powerful Excel VBA tip for you. Many a time before sending your workbook with Excel VBA Macro code in it, you want to save your workbook without any VBA code in it.
\nTherefore here in this article, I will show you – how to save your Excel Workbook without excel macro in it.
\nThis can be done manually as well as using Excel VBA.
\nAt the end of this article, I have a usage example of this VBA method. You can also download a FREE Excel Workbook<\/strong> for you to understand and play around.<\/p>\n

Method 1: Manual : Save a Copy of Excel workbook without Macro in it manually <\/a><\/h4>\n

Method 2: VBA: Save a Copy of Excel workbook without Macro using Excel VBA<\/a><\/h4>\n

Download an Excel Workbook to play around [FREE DOWNLOAD]<\/a><\/h3>\n

Manual Method: Save Workbook without Excel Macro<\/h1>\n

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.<\/p>\n

Step 1:<\/h4>\n

Go to File Option and Click on Save AS of your xlsm file as shown in the below picture:<\/p>\n

\"Excel

Excel Workbook with Excel Macro in it<\/p><\/div>\n

Step 2:<\/h4>\n

Now save your workbook as xlsx<\/em><\/strong> and not as xlsm<\/em><\/strong> as shown in the below picture<\/p>\n

\"VBA

VBA To SaveAs xlsx<\/p><\/div>\n

Step 3:<\/h4>\n

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.<\/p>\n

\"Confirmation<\/a>

Confirmation Popup – Save Excel without Excel Macro Code<\/p><\/div>\n

Step 4:<\/h4>\n

Now once you open your .xlsx workbook, you would not see any of the excel macro code as shown in below picture:<\/p>\n

\"Excel<\/a>

Excel Workbook Without Macro<\/p><\/div>\n

VBA Method: Save Workbook without Excel Macro using Excel VBA<\/h2>\n

Now as you have seen how to achieve this manually, I will show you how you can achieve this by using Excel VBA.<\/p>\n

Following VBA statement will be able to save your Workbook with Excel Workbook as a Workbook without any Excel VBA code in it.<\/p>\n

\nThisWorkbook.SaveAs Filename:=\"C:\\...\\abc.xlsx\", FileFormat:=xlOpenXMLWorkbook\n<\/code><\/pre>\n
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.
\nThis 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.<\/p>\n
Save Excel workbook as Excel Macro FREE Workbook without confirmation Popup<\/h2>\n
Refer the below VBA code to save your Excel workbook without saving any VBA code in it.<\/p>\n
\nApplication.DisplayAlerts = False\nThisWorkbook.SaveAs Filename:=\"C:\\...\\abc.xlsx\", FileFormat:=xlOpenXMLWorkbook\nApplication.DisplayAlerts = True \n<\/code><\/pre>\n
Example of usage of above Code<\/h1>\n
You have an excel workbook which has Excel VBA code inside it to generate a report or to perform any automated task. While sending the copy of that excel workbook, you do not want to send all the code, etc. inside it. In such case you can use above code in order to save the workbook as macro free before attaching to the email.
\nIf you want to know more about it, you can read this article here.<\/a><\/p>\n
Send current workbook as Macro FREE Workbook as an Attachment in email<\/h1>\n
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.
\nBefore 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.<\/p>\n
\nSub Email_CurrentWorkBook()\n\n    'Do not forget to change the email ID\n    'before running this code\n\n    Dim OlApp As Object\n    Dim NewMail As Object\n    Dim TempFilePath As String\n    Dim fileName As String\n    Dim originalWB As Workbook\n    Dim tempWB As Workbook\n    Dim tempXLSXPath As String\n    Dim tempXLSMPath As String\n\n    Set originalWB = ThisWorkbook\n\n    With Application\n        .ScreenUpdating = False\n        .EnableEvents = False\n        .DisplayAlerts = False\n    End With\n\n    ' Save your workbook in your temp folder of your system\n    ' below code gets the full path of the temporary folder\n    ' in your system\n    TempFilePath = Environ$(\"temp\") & \"\\\"\n    fileName = VBA.Left(originalWB.Name, (InStrRev(originalWB.Name, \".\", -1, vbTextCompare) - 1))\n    fileName = fileName & \"-\" & Format(Now, \"dd-mmm-yy h-mm-ss\")\n    \n    tempXLSMPath = (TempFilePath & fileName & \".xlsm\") ' with macro - as is\n    tempXLSXPath = (TempFilePath & fileName & \".xlsx\") ' without macro\n\n    ' first an as is copy of this workbook is created\n    ' and saved as with Macro in it [.xlsm] format\n    originalWB.SaveCopyAs (tempXLSMPath)\n    \n    ' Now open this copy of the current workbook\n    ' and saveAs a Macro FREE Workbook [.xlsx] format\n    Set tempWB = Workbooks.Open(tempXLSMPath)\n    With tempWB\n        .SaveAs fileName:=tempXLSXPath, FileFormat:=xlOpenXMLWorkbook\n        .Close savechanges:=False\n    End With\n\n    ' Now open a new mail\n    Set OlApp = CreateObject(\"Outlook.Application\")\n    Set NewMail = OlApp.CreateItem(0)\n\n    On Error Resume Next\n    With NewMail\n        .To = \"abc@xyz.com\"\n        .CC = \"abc@xyz.com\"\n        .BCC = \"abc@xyz.com\"\n        .Subject = \"Type your Subject here\"\n        .Body = \"Type the Body of your mail\"\n        .Attachments.Add tempXLSXPath ' attach .xlsx file\n        .Send   'or use .Display to show you the email before sending it.\n    End With\n    On Error GoTo 0\n    \n    ' Since mail has been sent with the attachment\n    ' Now delete both the temp files\n    ' .xlsx and .xlsm\n    Kill tempXLSMPath\n    Kill tempXLSXPath\n\n    'set nothing to the objects created\n    Set NewMail = Nothing\n    Set OlApp = Nothing\n    Set originalWB = Nothing\n    Set tempWB = Nothing\n\n    'Now set the application properties back to true\n    With Application\n        .ScreenUpdating = True\n        .EnableEvents = True\n        .DisplayAlerts = True\n    End With\nEnd Sub\n\n<\/code><\/pre>\n
*************IMPORTANT***********:<\/h1>\n
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?<\/em><\/strong><\/p>\n
There is a valid reason for doing so…<\/strong> Before I explain the reason, I would like you to read these two main differences between these two methods…<\/p>\n
Difference between SaveAs<\/strong> and SaveCopyAs<\/strong> in Excel VBA<\/h2>\n
Following are the two main differences between these two methods in Excel VBA…<\/p>\n
Difference No: 1 :<\/h3>\n
After running the SaveAs command, new workbook becomes the current Workbook [in VBA terms – ThisWorkbook]. <\/strong>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].<\/p>\n
While SaveCopyAs does it exactly opposite.<\/strong> Here original workbook remains open and copy is made as-it-is with the given file name and path.<\/p>\n
This is the reason…<\/strong> 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.<\/p>\n
Difference No: 2 :<\/h3>\n
Using SaveAs you can change many things around your Excel Workbook.. like
\n1. Secure it by providing a password
\n2. Change the FileName, FilePath, FileFormat, FileType etc.
\nany more…<\/p>\n
While using SaveCopyAs, <\/strong>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.<\/p>\n
This is the reason…<\/strong> 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).<\/p>\n
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.[\/et_pb_text][et_pb_cta title=”Download your Excel Workbook to play around” button_url=”\/excel\/wp-content\/downloads\/Send-Current-WB-As-Attachment.xlsm” button_text=”Download FREE Excel Workbook” _builder_version=”4.17.6″ _module_preset=”a50a16dd-d05f-4ea2-acab-1468d2e4010e” global_colors_info=”{}”]Download your Excel Workbook with Code – Send Macro FREE copy of the current Workbook. This you can use to learn and play around. Thanks.[\/et_pb_cta][et_pb_blurb title=”Did you like reading this article?” use_icon=”on” font_icon=”||fa||400″ _builder_version=”4.17.6″ _module_preset=”0249c68e-4de8-4f44-84ff-a9b1850785b6″ hover_enabled=”0″ global_colors_info=”{}” sticky_enabled=”0″]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.<\/p>\n
You can also read…<\/h2>\n