Programmatically convert a *.XLSM file to a *.XLSX file using VBA

Posted on 2010-01-08
Last Modified: 2012-05-08
Is it possible using VBA to save a *.XLSM file as a *.XLSX file programmatically.  Doing the SaveAs manually requires acknowledging a dialog that indicates all VBA will be lost in the process.  

Using VBA code like:
XLSX_Filename = TempFilePath & TempFileName & TempFileExt
wb2.SaveAs XLSX_Filename, FileFormat:=xlopenxmsworkbook

Produces the error:
Runtime Error 1004: "Method 'SaveAs' object '_workbook' failed

and using "Application.DisplayAlerts = False" does not seem to bypass the underlying dialog / problem.  

The goal is to build and email a temporary file using selected worksheets from a *.XLSM file and not include the VBA in the emailed workbook.    One option is to use the Microsoft Visual Basic For Applications Extensibility 5.3 as documented here: to remove all the VBA from the workbook being emailed but I dont know if our virus scanner will eventually cause problems with this type of code.      Alternate syntax or other options?

Question by:Jerry Paladino
    LVL 12

    Accepted Solution

    You know, the way I've done this is to ...
    1 - create a temporary workbook using workbooks.add
    2 - copy the selected sheets into it
    3 - email it out via the code from Ron DeBruin (google him)
    4 - kill the temporary workbook
    Works like a charm.  Good luck
    LVL 16

    Author Closing Comment

    by:Jerry Paladino
    Thank You....
    LVL 16

    Author Comment

    by:Jerry Paladino
    Thank you....  
    I was using wb1.SaveCopyAs filename and deleting worksheets to get the temp file.  Going the other way by using wb2.Add and adding sheets to it will work.

    Featured Post

    Live: Real-Time Solutions, Start Here

    Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.

    Join & Write a Comment

    The canonical version of this article is on my web site here: A companion presentation is available here:
    Article by: Leon
    Software Metering within our group of companies has always been an afterthought until auditing of software and licensing became a pain point. Orchestrator and SCCM metering gave us the answer and it was an exciting process.
    This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
    Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

    755 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

    19 Experts available now in Live!

    Get 1:1 Help Now