Excel 2016 Crashes, corrupts file, fixed with office 2013, need help troubleshooting

[pleeseemailme]

Howdy,
I have been running a number of processes at my company for years now (many were made possible from contributions from this community - thank you). I now have a new issue with files crashing Excel when loading into memory. The trouble is, it's not every file and its not every time. I was hoping some of you could help me troubleshoot this so I can at least know what questions to ask Google, or a forum or something. Here's what I know:

Files crashing excel and subsequently corrupting the file:
1. These files all run power query
2. they are network files
3. frequency of the crash is unpredictable, I have been unable to find a pattern/reason for the crash
4. opening the corrupt file in Excel 2013, saving and closing somehow fixes the file and the user can then reuse it.

Any help or guided questions would be most appreciated, thanks.

 

[sandy666]

1.always exactly the same files crashing?
2.do you trust all vba codes? (timebomb)

not wanting to waste time:
0. create backup of all important things
1. format C:\
2. install windows
3. instal Office (the same version that everyone has around)

 

[pleeseemailme]

Sandy, Thank you for the reply.

1. It is always the same type of file that crashes. Meaning, there are multiple managers running their own department copy of this file, and each one crashes about every 8-12 uses, or about once or twice a quarter.
2. Yes, we trust all VBA code without restrictions.

Solutions: formatting and reinstalling windows/office is not an option at this time in our business environment. However, I have ensured all managers are running the same versions of office and windows in an effort to solve this.

Thanks.

 

[sandy666]

1. I said: excatly the same files , not the same type of files
2. try turn off access to your personal book
3. create dummy file:
- with formula only and try
- with formula and PowerQuery and try
- with formula, PowerQuery and vba and try

@point 3 above: if all Offices around are different then yours

edit:

make copy of file crashing your Office
- remove vba (simply save as xlsx) and try
- remove PQ and try

 

[sandy666]

I forgot:
- make sure your temp folders are (because there are two) cleaned

 

[sandy666]

on the end you can try

run command prompt as administrator and type there:
chkdsk C: /F (it will check your C drive for errors and repair it if possible)
then run defrag

 

[pleeseemailme]

1. I said: excatly the same files , not the same type of files
2. try turn off access to your personal book
3. create dummy file:
- with formula only and try
- with formula and PowerQuery and try
- with formula, PowerQuery and vba and try

@point 3 above: if all Offices around are different then yours

edit:

make copy of file crashing your Office
- remove vba (simply save as xlsx) and try
- remove PQ and try

Sandy,
None of these managers have a personal workbook.
I would understand the idea of the dummy files and testing the variants of the files, if the file crashed and corrupted every time, but it doesn't. And, if it does crash, copying and removing the VBA by saving as .xlsx is not possible since the file is corrupt and cannot be opened.

I'm not trying to fix a corrupt file at the moment since they are all working; I'm trying avoid future crashes.

I forgot:
- make sure your temp folders are (because there are two) cleaned

I will check with IT, but I'm pretty sure these are cleaned already by their monthly update process on all our machines. Thanks.

 

[sandy666]

but you need to know the reason of crashes so start with the basics

 

[pleeseemailme]

Sandy,
I agree with you there. I guess I need more helping understanding how this will help. All files are currently working and are not corrupted. If I pick one, and make 3 versions of the dummy files and they all work like the main file does, what will that show me? If I wait till one corrupts, then I can't make the dummy files because I can't open the corrupt file in office 16. I can open it in office 13, but then that clears the code or memory or cache or something and the file is no longer corrupted, so then we're back to square 1 with a working file. What else do you suggest, or what am I not understanding? Thanks again for our patience on this.

 

[sandy666]

If I pick one, and make 3 versions of the dummy files and they all work like the main file does, what will that show me?

one file not 3 versions
with formula then add powerquery then add vba
it wolud be fine if you can use the same formulas, queries and vba codes as in original file (with personal or without personal - both option are possible)