Need to save xls workbook As xlsb with VBA

Question

I use a macro to create a daily report. The macro saves the xls report as xls historically. Due to large file size I want to save the report as xlsb. Two problems. The macro script i am using will run but I cannot open the xlsb file later. Message received is

"Excel cannot open the file'RDN Activity Report.xlsb' because the file format or file extension is not valid. Verify that the file has not been corrupted and that the file extension matches the format of the file.

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 a file when file name has date in file name and date of file is yesterday's date such as "RDN Activity Report.20150726"

Answer 1

Use SaveAs parameter FileFormat:

• 50 = xlExcel12 (Excel Binary Workbook in 2007-2013 with or without macro's, xlsb)

• 51 = xlOpenXMLWorkbook (without macro's in 2007-2013, xlsx)

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

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

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