Remove VB code from saved Excel file

Posted on 2012-09-18
Last Modified: 2012-09-18
My excel macro is executed from command line using Workbook_Open()

At the end of the macro, the template file from where the code is running, saves a output file with date time stamp.
But the output file has the code as well, and when I open the output file, Workbook_Open() executes again with error.
How can I save the output file with out copying over the VB code/macro?
Question by:kris_sk2012
    LVL 31

    Expert Comment

    by:Rob Henson
    Which version of Excel?

    If using 2007 or later the original file will be an xlsm (macro enabled). If you save as an xls it will save without the macros.

    Rob H

    Author Comment

    I'm using Excel 2010.
    The code in the VB script that saves the new file is

     ThisWorkbook.SaveAs ThisWorkbook.Path & "\Report_" & getDateTimeStamp() & ".xls", XlFileFormat.xlExcel8

    What needs to change above to save the workbook without macros or code carried over?
    LVL 4

    Expert Comment

    Hi kris,

    You should try copying the workbook and then saving the copied version which doesn't include the VB code. Please refer to the last post by "johnske" here:

    LVL 33

    Accepted Solution

    Save the workbook as xlsx.
    Application.DisplayAlerts = False
     ThisWorkbook.SaveAs ThisWorkbook.Path & "\Report_" & getDateTimeStamp(), XlFileFormat.xlOpenXMLWorkbook
    Application.DisplayAlerts = True

    Open in new window


    Author Closing Comment

    Works perfect!!!

    Featured Post

    IT, Stop Being Called Into Every Meeting

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    Join & Write a Comment

    A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
    How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
    The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
    This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

    730 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    18 Experts available now in Live!

    Get 1:1 Help Now