• 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?

Thanks,
Jerry
0
Jerry Paladino
Asked:
Jerry Paladino
  • 2
1 Solution
 
sdwalkerCommented:
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
sdwalker
0
 
Jerry PaladinoAuthor Commented:
Thank You....
0
 
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.
 
0

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