Stop xlsb from running in compatability mode

[jomili]

The snippet of code below is what I'm using to copy the activesheet from an XLS workbook to a new workbook, then saving it as an XLSB, which then is used for interacting with various other xlsb workbooks. However, when I try to copy a whole sheet from one of those XLSBs to my new XLSB, I get an error that I can't paste that many rows. My new XLSB is the only workbook in my collection that says it's running in Compatibility mode. Why is it running in compatibility mode, and how can I fix it? Thanks.

Please Login or Register  to view this content.

[abousetta]

I haven't tested this but from what I have read, you can either open the file in non-compatibility mode

Please Login or Register  to view this content.

Or

change your global setting to open xlsb files in non-compatibility mode (http://bensullins.com/excel-tip-defa...tibility-mode/)
P.S. with Ben's example you would probably need t change the .xlsx to .xlsb before saving.

Hope this helps.

abousetta

[jomili]

But I'm not OPENING an xlsb in compatability mode, it's somehow being CREATED in compatibility mode. In an xlsb workbook I have a macro that copies a worksheet from an XLS workbook to a new workbook, and saves it as an xlsb. Since the XLS files are opened in compatability mode, the child of one of those, even though saved as XLSB which shouldn't require compatibility mode, runs in compatability mode. I'm afraid if I change my global setting for Excel to non-compatibility it wouldn't play with all the other xls files I have to work with.

I can work around this by having a default xlsb workbook already created, but I'd rather see if there's a way to get past this more elegantly.

[abousetta]

To the best of my knowledge, compatibility mode only kicks in when opening a file, not on saving a file. In other words there is no way to save "Excel, as you save this file, make sure when it opens in Compatibility Mode". Therefore this issue should only be appearing when the saved file is being opened up after being saved. Of course I may be completely wrong

[romperstomper]

If you copy a 97-2003 format sheet as a new workbook, it will remain in compatibility mode even after saving in an xml format until you close and reopen it.

[jomili]

Thanks Romper; that's consistent with what I'm seeing. So, I guess I'll have to figure out how to close/reopen it without knowing it's true name.

[romperstomper]

If you saved it, you know its name, no?

[jomili]

Well, I guess I do. It's on the user's desktop, but it's a consistent name, so easy enough to grab back. Thanks for pointing me to the obviouse.