Trying to open misbehaving XLSM file: eliminating macros as a cause?

[EdNerd]

Recently we've come across a problem with certain XLSM files, and only this one class of files. When certain users attempt to open certain files, all of Excel - the application and any other open Excel files of any kind - simply and abruptly close. The macro-filled template for the misbehaving files was built 10-15 years ago, and updated every now and then with added functions. I suspect a few factors might be involved: something corrupted in the file, old spaghetti code (all mine!), updates to Windows, Excel, and security patches, and older computers with lower processing speeds and capacities. I'm just a self-taught VBA dabbler, and have no clue what might be causing the log jam.

Finally, I have one such file on my computer to examine. I was trying to eliminate the AutoOpen macros as a factor, and tried to open while holding down Shift. The file still crashed the application. Does the SHIFT key trick still work?? Then I tried changing the file extension from XLSM to XLSX. But got an error that the file extension didn't match the file format. I'm guessing because internally it's still an XLSM file with a VBA component? I even tried making a ZIP file, then deleting everything in the VBA module, and putting it back together as a file - but still no go.

Is there a sure-fire way to open this without the Auto macros firing? Or to eliminate the macros from the file? I'm just trying to eliminate macros as a possible cause of the crash.

 

[6StringJazzer]

Try putting the file in a location where macros are not allowed. I can't be more specific because I don't know your security settings. Or you could just globally disable macros. Then when it opens, do not allow macros to run. If it opens without crashing, save it as a .xlsx file. If it still crashes Excel then you may have some truly insidious corruption.

However, you said "certain users." I would survey these users to determine what is in common among their environments.

 

[EdNerd]

Hi, Jeff. Thanks for responding.
-- If I put the file in a server folder, the file opens in Protected view. I get a yellow banner on top with a "click me" to "Enable Editing". Before I click that, the file is open, macros did not run, but SaveAs is not available.
-- When I do enable editing, Excel crashes completely.
-- I am not able to change macro settings - that's controlled by IT.
-- There is nothing common among these users. They have different computer models and different competencies. A file that crashes one person or one computer doesn't for someone else. A file the user opened every other day and even that morning is now crashing. No apparent rhyme or reason.

 

[MARK858]

Hold Ctrl while opening Excel via double click so it is in Safe mode, click yes to the message box that should appear
or

I was trying to eliminate the AutoOpen macros as a factor

Hold the Shift key while opening in File Explorer just to stop the autoopen/Workbook_Open firing

 

[EdNerd]

Hold Ctrl while opening Excel via double click so it is in Safe mode, click yes to the message box that should appear
or

Hold the Shift key while opening in File Explorer just to stop the autoopen/Workbook_Open firing

Neither the Shift nor the Ctrl tricks worked.
The only way I can open these files is to use the Open and Repair function, then do a SaveAs. The repaired file keeps the macros and opens okay. I just can't tell what was changed in the repair. Excel says there's a log, but it never has anything more than a generic "the file was repaired" message.