VBA Program To Save XLS Files From Excel 2010 Makes File Corrupt
Filed under: How To Avoid Corrupt XLS Format In VBA When Saving From Excel 2010
For VBA Excel programmers: If you are running Excel 2010 and want to programmatically save a file as an XLS, you need to be explicit about the format. Otherwise the file format will default to XLSX and if you save it as an XLS it may be tagged as a corrupt file when you open it later.
To specify the XLS format type, specify the Fileformatnum as 56. Here is a short coding example:
Dim wb As Workbook
Application.DisplayAlerts = False
Worksheets(sheetname).Copy
Set wb = ActiveWorkbook
‘ Fileformat 56 is for the older XLS file types
wb.SaveAs filename, 56
wb.Saved = True
wb.Close True
Application.DisplayAlerts = True
Here are some other formats.
51 = xlOpenXMLWorkbook (without macro’s in 2007-2013, xlsx)
52 = xlOpenXMLWorkbookMacroEnabled (with or without macro’s in 2007-2013, xlsm)
50 = xlExcel12 (Excel Binary Workbook in 2007-2013 with or without macro’s, xlsb)
56 = xlExcel8 (97-2003 format in Excel 2007-2013, xls)
More coding examples can be found here: http://www.rondebruin.nl/win/s5/win001.htm
