You must save the xls book. As xlsb with VBA

Question

I use a macro to create a daily report. A macro saves an xls report as xls historically. Due to the large file size, I want to save the report as xlsb. Two problems. I am using a macro script, but I cannot open the xlsb file later. Received message

"Excel cannot open the file" Report Report.xlsb "ReportNX" because the file format or file extension is not valid. Make sure the file has not been corrupted and the file extension matches the file format.

txtFileName = Format(Date - 1, "yyyymmdd") ActiveWorkbook.SaveAs Filename:= _ "\\Clt-stor01a\CA_Services\RDN Reports\ForUploadPrev\RDN Activity Report." & txtFileName & ".xlsb", _ FileFormat:=xlNormal, Password:="", WriteResPassword:="", _ ReadOnlyRecommended:=False, CreateBackup:=False txtFileName = Format(Date - 1, "yyyymmdd") 

Note. I also need a script that can open the file when the file name has a date in the file name and the file date is yesterday, for example, " RDN Activity Report.20150726 "

Answer 1

Use the SaveAs FileFormat parameter:

• 50 = xlExcel12 (Excel Binary Workbook 2007-2013 with or without macros, XLSB)

• 51 = xlOpenXMLWorkbook (no macro in 2007-2013, xlsx)

• 52 = xlOpenXMLWorkbookMacroEnabled (with or without a macro in 2007-2013, xlsm)

• 56 = xlExcel8 (format 97-2003 in Excel 2007-2013, xls)

   ActiveWorkbook.SaveAs "C:\temp\text.xlsb", fileformat:=50