Darryl Collins
darryl at whittleconsulting.com.au
Tue Sep 11 01:32:45 CDT 2012
Hi Charlotte .xlsb is the binary format for XL2010/2007. It supports VBA like an xlsm - it has huge advantages as it saves files much smaller/faster than .xlsx or .xlsm (a saving of 50%+ is usual) and the workbook is much more responsive with open / close / save etc. Saving Excel files using .xlsb (binary) will give you the best response speeds and smallest file size. Charles Williams is the guy who runs "Fast Excel" and co-wrote the "Name Manager" Add-in amongst others has run some tests and determined (on average) that .xlsx formatted files load 4 times slower than .xlsb; save 2 times slower and has 1.5 times a bigger file size upon save. These figures are very consistent with the sort of behaviour have experienced. A UK based Excel MVP I talk with from time to time also confirmed: "In my experience xlsb is faster than xlsx and xlsm to load and save, and in many cases the file sizes are considerably smaller. I have one file which was over 40MB as an xls, a little under 30MB as an xlsx and only 15MB as an xlsb. It's a lot faster to open as an xlsb too. I have certainly had fewer problems with my personal macro workbook since I converted it to an xlsb rather than xlsm. Functionally there is no difference - it's merely a question of how they are stored (all are compressed, but the xlsb folder contains binaries rather than XML). The primary purpose of the xlsx (and xlsm to some extent) formats was to make the structure 'open' - i.e. you can read them with a simple text editor or anything that understands XML. XLSB obviously you can't really do that. I'm not generally worried about that so I tend to save as xlsb." The performance advantage is considerable. 1.5 times smaller, 4 x faster on open etc... Provided you can leave the XLM format behind (some EU places have legals about this, which is at least part of the reason for the XML code to start with) I find it is well worth it. I guess the only other 'gotcha' maybe that some folks will see .xlsb and wonder what the hell is it - My Mum would probably delete it thinking it must be a virus for example ;) ... Cheers Darryl. -----Original Message----- From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Charlotte Foust Sent: Tuesday, 11 September 2012 2:54 PM To: Access Developers discussion and problem solving Subject: Re: [AccessD] A2010 and export to XL as .xlsb Darryl, I'm not even sure what an .xlsb file is for. Why were you exporting to that particular format? Charlotte On Mon, Sep 10, 2012 at 5:22 PM, Darryl Collins < darryl at whittleconsulting.com.au> wrote: > Hi guys, > > Was exporting a query as an .xlsb and Access really didn't like it. > Took a few seconds as an .xlsx, but doesn't happen at all with xlsb. Odd.... > Anyone else get this / seen this? > > Darryl Collins > Whittle Consulting Pty Ltd > Suite 8, 660 Canterbury Rd > Surrey Hills, VIC, 3127 > > p: +61 3 9898 3242 > m: +61 418 381 548 > f: +61 3 9898 1855 > e: > darryl at whittleconsulting.com.au<mailto:darryl at whittleconsulting.com.au > > > w: www.whittleconsulting.com.au > > <http://www.whittleconsulting.com.au/ > > > > > -- > AccessD mailing list > AccessD at databaseadvisors.com > http://databaseadvisors.com/mailman/listinfo/accessd > > > Website: http://www.databaseadvisors.com > > > -- AccessD mailing list AccessD at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/accessd Website: http://www.databaseadvisors.com