Menu
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"
748
The XLSB format supports macros. This is in contrast to the XLSX format; in the OOXML family of XML-based formats, the . xlsm extension must be used for spreadsheets with macros. The specification for XLSB is frequently updated.
xlsb) saves the data within an Excel workbook in binary format instead of Extensible Markup Language (XML) format. Binary (i.e. 1s and 0s) is much more efficient that human-legible XML, so the result is a much smaller file size when saved on disk.
To save an Excel workbook using VBA, you need to use the SAVE method to write a macro. And in that macro, you need to specify the workbook that you want to save and then use the SAVE method. When you run this code, it works like the keyboard shortcut (Control + S). Specify the workbook hat you want to save.
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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
