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

EdNerd

Active Member
Joined
May 19, 2011
Messages
464
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.
 

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes
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.
 
Upvote 0
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.
 
Upvote 0
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
 
Last edited:
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,224,545
Messages
6,179,432
Members
452,915
Latest member
hannnahheileen

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top