• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1557
  • Last Modified:

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

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: http://www.cpearson.com/excel/vbe.aspx 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?

Jerry Paladino
Jerry Paladino
  • 2
1 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
Jerry PaladinoAuthor Commented:
Thank You....
Jerry PaladinoAuthor Commented:
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

Get quick recovery of individual SharePoint items

Free tool – Veeam Explorer for Microsoft SharePoint, enables fast, easy restores of SharePoint sites, documents, libraries and lists — all with no agents to manage and no additional licenses to buy.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now