Excel crashes when opening a specific (complex) .xlsb file (and all previous versions of it too)

[Jaspos]

I have a business critical Excel .xlsb file, that I have been updating/using for years (it contains about 20 sheets, and a lot of VBA code - none of which runs on opening).

Yesterday, the file refused to open on 2 different work PCs, both running the same, fully updated version of Excel from an Office 365 E3 business subscription. Whilst I thought this one file may be corrupt, I have used this file for many years, with many older versions on the file saved (some of which have not been used/opened for years) - and Excel was suddenly unable to open these versions either!? When trying to open the file, Excel would simply freeze for a bit, then close with no error messages. Opening Excel in safe mode made no difference, same issue. Further info on this led me to believe the problem was with the latest version of Excel as:

- opening the file on a laptop with a less recently updated version of Excel worked fine

- I could preview the file fine in the Windows Explorer preview pane

- by opening the file on the laptop, and saving it as an .xlsm file it worked -the .xlsm file can be opened on all PCs

- NB I could NOT open the .xlsb file in Office 365 via the browser, I got an error message saying it needed to be viewed in the desktop (which of course didn't work either)

So there is something in this file (and previous versions) that the latest version of Excel/Excel online does not like. NB other .xlsb files I have so far seem fine.

This looks very similar to another user's issues, reported in Jan 2020 (which appears unresolved):
https://techcommunity.microsoft.com/t5/excel/excel-workbook-xlsb-crashes-on-new-computer-and-latest-...

Can anyone shed any light? I now have a working .xlsm file - but I was only able to do this as I had a laptop with a different version of Excel on - if this wasn't the case then I could have been scuppered. And it makes me wary of using .xlsb files full-stop for now!

[#2]

@Jaspos 

There are several possible reasons why Excel crashes when opening an .xlsb file, such as corrupted cache, outdated software, incompatible add-ins, conditional formatting, antivirus interference, or system issues.

Some of the solutions that you can try, perhaps you have already tried a few, here with the order:

• Restart your computer and try opening the file again.
• Run Excel as administrator and see if it can open the file.
• Install the latest software update for Excel and Windows.
• Clear Microsoft Excel cache by deleting all files in %appdata%\Microsoft\Excel folder.
• Remove conditional formatting from your file by using Format Painter on a blank cell and applying it to the entire worksheet.
• Close unused applications that may be using up memory or resources.
• Disable your antivirus temporarily and check if it affects Excel’s performance.
• Update your system drivers and BIOS to ensure compatibility and stability.

If none of these solutions work, you may have a corrupted .xlsb file that needs to be repaired.

You can try saving the file in an old format (.xls) on a different computer that can open it, then re-saving it as .xlsb. This may fix some of the corruption issues.

You can also use a third-party tool to repair your .xlsb file, such as Stellar Repair for Excel or Kernel for Excel Repair. These tools can scan your file and recover data from damaged worksheets. Have not used these tools myself, but are recommended by some, so without guarantee.

I hope this helps you resolve your issue.

NikolinoDE

I know I don't know anything (Socrates)

[#3]

Hi @NikolinoDE

Thanks for the response, I have tried some of this, but as per my post, I think these are highly unlikely to be the problems, to reiterate:

- the file opens fine on a laptop with an older version on Excel, so it is not corrupted
- even if it WAS corrupted, how could all the old versions of the file, which had not been opened in some cases for over year, corrupt at the same time (saved on different drives, local and the cloud)
- the file suddenly stopped opening on 2 separate PCs (with different hardware, graphics cards/drivers etc), on the exact same day, so it is highly unlikely this problem is PC related - how could they both go wrong in the same way at the same time? It could maybe be a virus issue, but:
- the file will not open in Excel in the browser either (where it would before) - as a browser version of Excel it is not affected by any hardware issues, except those affecting the browser itself.

So it seems to me the most logical culprit is Excel itself, both desktop and that running in the browser.

[#4]

@Jaspos 

Hi, did you ever get this resolved? I seem to have a similar problem where my .xslb file immediately closing when opening with no error message. Excel then does a repair and it works. But after a while making some macro changes, it happens again.  I have tried removing all macros and reloading. Same problem after a while. I am on Windows 10, Office 360 16.0.16501.20074 64 bit.

BTW. My Workbook has 82 sheets and 276 marcos.

[#5]

@Duwat13 Sorry no. I was able to convert the file to .xlsm and all was fine. It did take a bit of sorting, but if you are able to open the file occasionally then I'd do this. For me, the only real advantage to a .xlsb file was smaller file size, but not much of an advantage if it doesn't work....!

[#6]

@Jaspos 

Little late to the piece but I had (and still have on occasion) this issue and use the following 14 steps to resolve it:

1. If open close all workbooks and Excel
2. Open Excel
3. Follow the path File > Options > Trust Center > Trust Centre Settings
4. In the left-hand panel of the Trust Center dialog click on Macro Settings
5. Use the Snipping Tool to take a screen shot of what the current macro settings are
6. Click the Disable VBA macros without notification radio button and unselect any other option(s)
7. Click OK twice
8. Open the binary file that is automatically closing upon trying to be opened which should now open. If still doesn't I have no idea I'm afraid 
9. Go into your code and follow the menu path Debug > Compile VBA Project
10. Fix any syntax errors and when the code compiles without issue follow the menu path File > Close and Return to Microsoft Excel
11. Save and close the binary file opened in step eight
12. Close Excel
13. Open Excel and reset the macro settings as they were from the screen shot taken in step five
14. Open the binary workbook from step eight

Hope that helps.

Regards,

Robert

[#7]

@JasposHello,

maybe this help is not too much but look at it:

I tried it several times in a situation like the one below and it works.
- My Excel is a very complex form of VBA, functions and automation in every area even for several external files (also files accessed with autogenerated name related to data in sheets which isn't possible as M$oft said - yes it is possible)
- I have such a habit to check after saving whether the file continues to open (Microsoft software in most cases is very defective and unprofessional compared to Apple)
- one day I open the main file and what do I see - crash without warning, totally nothing helped, neither cache nor reinstallation completely nothing. The file seemed to be damaged
- Another habit is always making a backup before opening the file for further work (usually adding next version number)
- renaming the corrupted file to another file - I open and it works! Excel has not crashed. I just change file name!
- back to the exact previous name - Excel crash! Vvery professional amateur problems in m$ but predictable
- rename the corrupted file again - I open and it works, no crash
- just to be sure, I return to the previous version of the name, again crash, when I duplicate this file and it already has the note "- Copy" ... copy also crash
- I delete the copy because there is no point
- I rename the original file to anything else - the result - it opens, no crash! exactly as before...
- after opening it saves via Save As... under the name I use in automation (the same one which I found out my file is damaged!). I close everything including Excel
- I open the newly saved file with the option Save as... - and it works! no crash :)

Very strange but after all this the newly saved file is smaller by about 25% in bytes. Everything in it is okay, VBA works and, the file looks like recovered.

The question is what kind of garbage Excel professional programmers include to saving for themselves, and probably their teams of programmers don't even know what kind of mess they are making, how to fix it, so they ignore it.

I have noticed this many times whether in Word, PowerPoint and especially Excel re-writing a document clears or deletes some information (in Excel its really a lot!). I don't know what kind but in this particular situation it helps more than 3 times with different files.

This is an age-old problem of Windows, a very amateurish and failed system written by amateurs. They just copy ideas without knowing how to do it right. It's just a shame that such a very successful - the only (!) - M$oft product started from Lotus, today Excel has such defects and problems. This type of situation never happened on the version for Mac OS X Excel 365 and to buy for not much money. Does the benefit of Apple's brilliant, extremely stable and secure system help?

Regards,

and I believe it help someone :)

[#8]

@Jaspos A friend had that issue yesterday. He brought me the machine...The change of printer didn't help because maybe the machine was not connected to any active printer...

The simple solution I found was to disable protected view settings by clicking on the phrase:"Be careful-files from the internet.....in protected view" by the side of Enable Editing. 

In the Protected View Orange box, click on Protected View settings.

Disable what it necessary and get your work done...

You can go back and enable protected View when you are done.

[#9]

@Orghal Today I had a very similar problem. A complex Excel powerquery file (no VBA though) suddenly freezing Excel every time I open it (Windows 11 + Office 365). Strange enough, the same file would open fine on another PC (with very similar setup Wni11 / Office 365). 

And COPYING the file did also the trick ! Note that just changing the name of the file was not enough.

If not reading that post I would have never tried it. 

So thanks a lot but so puzzled by such MS quirk!

[#10]

Hello,
Unfortunately, the problem is starting to occur more and more often. In recent days, I have lost many files and automations in completely unexplained situations. When this huge giant Microsoft BUG takes place there is no way to work even with older files - it's almost as if an infection worse than Covid called Windows has spread all over the disk and all the files. Trying to reinstall Office doesn't help anymore either, a huge amount of work is lost and M$oft does nothing about it. This is amateurism unworthy of any professional.

I am trying to push in the company (huge company 300k+ employee) the necessity of a full switch to Mac OS X and a completely new direction with software including the development of my own - which is not so difficult at all. It took me literally 4 weeks alone to write a replica (full) of an older Microsoft Word in object-oriented Basic (the way m$ first creates its software), along with recreating the entire interface.

M$oft is ruining the best program ever created since Lotus and here the gate is closing. Analysts and programmers like me can't waste time solving problems with the sorry idiots at this primitive little company.

Patience is over and not at least in my department has already withdrawn all licenses for m$oft products replacement of hardware is also beginning for professional fully stable and highly reliable macbook pro. Of course, hundreds of times faster than anything running on Windows, which we can already see in the differences on, for example, the edition of Office 365 on Mac OS X, but this is just for testing. I hope that the transformation will not take us long and it will be like Google - Windows to the trash.

I wonder how long it might take me to create a fully usable replica of the ordinary Excel LTSC 2021, I suspect no more than 5 weeks as long as I want to reproduce all the functions better and faster working and, above all, without unexpected crashes without any explanation - if they occur - for user. No disappearance of applications and watching the desktop where only the hands fall down.

[#11]

@NikolinoDE 

Its been my experience that Office 64 bit has serious defects, and back in 2011 or so they published on their developer website no intention of fixing the known issue causing your file to automatically close. It has to to with VISUAL BASIC. They released office with a 32 bit VB in it, despite being 64 bit office. Bug #1. They also have an unwanted Book1.xlsx that the save process will automatically create, and it is unneeded and causes the error. The VB code gets saved correctly in the xlsm file, then it adds the unwanted hidden file in error. This goes way back to Office 1998, then they thought having one file hidden that would contain all your macros was a good idea. They still have snippets of code in office that they cannot fix or locate that is trying to still save this hidden file. This is then causing your machine to open the xlsm file today, and it acts like there is an error in the file. There are no errors with your file. Just OPEN EXCEL first, a blank workbook, then click FILE OPEN and browse to your file, then in the dialogue where it sayd OPEN inthe lower right, click the tiny down errors, and use Open and Repair. It will open the file file and get rid of the unwanted file that windows adds as a brazen defect of windows .

[#12]

@Ted_power_user_Maine 

This sounds interesting and could be the problem with my .xlsb file. It is around 12MB with 80 worksheets and 250 macros. 

After some  saves, when reopening it crashes with no message. Then opening after crashing it does repair and seems to work okay. I always now keep saving after every minor change so that if I have a crash I can go back to previous saved version that doesn't crash. It does mean I end up with hundreds of saved versions! Very annoying!

I have to constantly check after I save a version if it will open without crashing. I was thinking it could be a memory issue as sometimes (rarely) when saving Excel says something about Out of Memory. However I can't find any reason for why sometimes it gets corrupted and crashes without any error message. 

[#13]

@Duwat13 Definitely good idea need to try it and check for unneeded files. Good suggestion. From my side I can include that Microsoft never fixing real bugs because peoples and teams not work so long in this fake company. Guys which create it or know something they are gone from m$oft and they don't have any documentation. In the times of Windows 3.1 I wrote in Visual Basic for Atari Falcon completely replicate Word with exact the same GUI in 2 weeks, so can imagine what kind of mess they have in m$oft to make any positive actions and fix this problem (as many other) asap? Impossible, amateur company and programmers, that's all about any software from m$ created for it's own operating system windows. I am just thinking why 365 for Apple Mac OS X do not have such a problems, with the same files I never detect there is bugs. M$oft is like a covid, this is the first virus in the World not any thing else.

[#14]

Hi,
no problem. I still fight with this bug and one more thing I read here - hidden file. Need to check what is it because in my opinion m$oft never fix any bugs. Any one who is called programmer and coming from this fake company should be kicked out and redirected to selling fishes in any simple market.

There is additional trick which I use last 2 months and cannot understand but it helps.

My code at all in one automation has already 2700 code lines (paragraphs count, real pages code it is more than 6k). Every time before I go to close this workbook after never mind what I do, change or update - even if it's just functions or sheets graphic form changes, and not VBA, anything. I always opening editor with largest module (around 1k lines). Select all -> Cut (Control+X) -> Close module -> open it again -> paste source as it was before with removing last 2 empty lines always appeared at the end (maybe m$oft still don't know how to make proper copy-paste action). Then I saving workbook file and closing it. Reopen and there is no problem, simple test click any button and no errors about disabled macros or not existing sources.

This operation do not provide to crash and unidentified disappearance of source codes not known where from the document. That's why also all the time I copying all sources (Modules) to separated Notepad and save as backup.

Sometimes Excel is screaming when you run any code from buttons, actions etc. which sounds like "macros disabled or not exists". What help here? very simple... of course if you can see any modules in Editor just take largest one and re-copy-paste sources as it is without any changes (only those two empty additional lines to remove). In my situations it always works 99.99%. Maybe once for 100 situations excel crash just like before so I re-copy all from notepad and workoook back to work. Don't know why, how and what kind of idiot in m$oft create it. Must be total amateur and perfectly not skilled dumb. Every thing 100% what m$oft touch is always destroyed without any exceptions.

Sad, no one from this amateurs and managers read this forums and will never take into consideration our experience and very long experiments on how to prevent problems. This should help locate the problem and fix it. After all, they must still have somewhere the source codes of their software and someone can check it. Just a child 12-15 years old is enough, because he will already have more experience in coding than a m$oft employee.

Regards to all

[#15]

Hahaha NikolinoDE I think you must be from call center, m$oft RTS we call it - Restarting Team Support :) ... I suggest additional step when you click those Restart option in Windows, use left hand to punch you vaccination point and right hand to hit your forehead - in the same time, so you can click mentioned restart with nose properly :) Maybe in lucky time BIOS be updated also :) If you lost vision in this complicated action, you need to change graphic card and connect next pipe with helium or water. Good luck.

[#16]

@Orghal 

Unfortunately, with so many posts without more detailed information and inappropriate comments, I can only provide general information that I think is correct.

First of all, I am not an employee of any call center or Microsoft. I'm a simple user like most people here. My answers are voluntary and without guarantee.

To be clear, not everyone has the same problem, even though it might seem so. That's why it's always recommended to publish a new post, as everyone can have a different hardware and software configuration.

If a lot of things are unclear from the start regarding the hardware and software configuration, it is good to give general step-by-step instructions that ignore the basic sources of errors.

In this link you will find some more information about it:

Welcome to your Excel discussion space!

At the same time, I ask you to behave politely and not to make yourself important with personal direct or indirect insults.

I will no longer participate in this post, thank you for your patience and understanding.

[#17]

@Orghal1045 

I am also using very large EXCEL files, with a lot of VBA code, many named ranges, and it contains many intended links to other EXCEL files.
For several years my EXCEL file crashed up to 10 times a day for no particular reason at random moments and sometimes it could no longer be opened.

I have been able to solve it by following 2 measures (one work-around and one true solution:(

1. Workaround: I created a VBA script that is started upon opening of the EXCEL file and that I configured to run when I press Ctrl-R (which stands for 'Archive' to me) which I do regularly while working in EXCEL. Basically, this VBA script does two things:
   • it copies the EXCEL file to a separate file with date/time postfix in separate directory (in my case to the .\BK\ subdirectory), so that I can always revert to any previous instance of the document
   • it saves the currently open EXCEL under the original name
2. True solution: At a certain moment I was completely fed up with the situation and I decided to gradually remove parts of my document's content, VBA, and external links. 
   It appeared that referring to a Named Range in an external EXCEL file was the sole root cause of my instabilities. After having replaced them by cell-based range references, all my instabilities were solved at once!

In my case, after having implemented my 'true solution' I have now been able to work without serious stability issues for about 4 years.

As instabilities for other users can have different root causes, my true solution (2.) might not help you, but this suggestion might help others.

My work-around (1.) can be a helpful feature for everyone who does (or does not) experience any stability issues with EXCEL.

Hopefully, EXCEL users can benefit from these experiences.