Using .xlsb (binary) vs. .xlsm files

[PaulZak]

Hi folks,

Our file's size has finally exceeded 10 mb and thus now gets rejected by our Organization's email virus scan step. If we zip or compress it to .zip file that's about 6 mb, the file still is rejected due to the 10 mb limit. So to reduce the file's size, I've tried converting it to an .xlsb file and the size dropped to 6.7 mb! The .xlsb file also made it through the email/scan limit and seemed to open & run fine during some very basic testing.

But I'm hesitant to switch to this file format "permanently" because I'm not familiar with it and don't see it used often. So I'm reaching out here to all the experts for some anecdotes, warnings, or encouragement with this decision. The only issue I've read about so far had to do with running a .xlsb file edited in Excel 2013 in Excel 2007 (= backward compatibility issue?).

Our .xlsm file has a custom ribbon, many user form objects, much VBA, and connects to a back-end Access db file. I don't want to make the switch and then find out months later that this file format is a problem and then have to re-do any recent code changes to the old .xlsm file.

As far as VBA & the Ribbon are concerned, are the .xlsb and .xlsm file formats basically the same?

 

[xenou]

I generally use xlsb as my preferred file format. There is no functional difference that you will notice in practice. In short, no worries My only reason for preferring xlsb is that I use a lot of vba and I don't want to have to to worry about changing the extension if at some point I decide to add a code module. Supposedly xlsb will load faster. But both workbook formats are 100% compatible with all Excel features.

 

[jsotola]

xlsm and xlsb both allow macros

each of these file formats is actually a zip file
( tack the extension .zip on the end of filename eg. test.xlsm.zip and it will open as a compressed folder in windows when you double click it)


xlsm is filled mostly with clear text xml files that can be viewed with a text editor

xlsb is filled with the same files that have been converted to a bin format (total gibberish when viewed with a text editor )

 

[PaulZak]

Thank you both for the input.
I feel a bit more confident now using the .xlsb file alternative.

 

[cmala]

Hi folks,

Our file's size has finally exceeded 10 mb and thus now gets rejected by our Organization's email virus scan step. If we zip or compress it to .zip file that's about 6 mb, the file still is rejected due to the 10 mb limit. So to reduce the file's size, I've tried converting it to an .xlsb file and the size dropped to 6.7 mb! The .xlsb file also made it through the email/scan limit and seemed to open & run fine during some very basic testing.

But I'm hesitant to switch to this file format "permanently" because I'm not familiar with it and don't see it used often. So I'm reaching out here to all the experts for some anecdotes, warnings, or encouragement with this decision. The only issue I've read about so far had to do with running a .xlsb file edited in Excel 2013 in Excel 2007 (= backward compatibility issue?).

Our .xlsm file has a custom ribbon, many user form objects, much VBA, and connects to a back-end Access db file. I don't want to make the switch and then find out months later that this file format is a problem and then have to re-do any recent code changes to the old .xlsm file.

As far as VBA & the Ribbon are concerned, are the .xlsb and .xlsm file formats basically the same?

Using .xlsb seemed very promising for a certain application involving programmatically copying a .xlsm template file and inserting via TransferSpreadsheet method information into the copied file. However the inserting part failed due a message of "read only" file privileges, in the new file. No such "read only" privileges has been explicitly set in the copied template file.

 

[ssddeell]

Although this is an older post, it is still relevant. In Excel 2013 I have found no differences in functionality. Many comment on how .xlsb is faster to load and creates smaller files sizes than .xlsm. Per my experience with an 11MB file that has a lot of formulas and VBA code, the .xlsb file is actually 3.3% larger, but loads 22% faster. Per all this, I use .xlsb.

 

[SpillerBD]

"XML offers a number of advantages. Because an XML file is a simple text file, any program can easily read data from it. This file format is also less prone to corruption than BIFF. If you randomly wipe out several bytes of a BIFF file, it is likely that the file will be corrupt and no longer open in Excel. If you truncate or corrupt several bytes of an XML file, the rest of the data is still readable in Excel."
"Files stored in BIFF12 have an .xlsb file extension."
"BIFF12 suffers from the same problems as all previous BIFF versions: It is difficult for other applications to read from or write to BIFF formats, and if parts of the BIFF12 file become corrupted or truncated, Excel has a difficult time successfully loading the file."

From "Excel 2013 in Depth" by Bill Jelen.Page 134

 

[ssddeell]

Thanks for the additional info which I had seen before. I should add that I use .xlsb for sharing the file. That is, I keep the master file that I edit in an .xlsm format for the above potential corruption issues. I then convert that to .xlsb when I shared it for the file speed advantages.