Friday, April 22, 2016

Excel Crashing Sometimes When Closed from Shared Network Location

By Hường Hana 5:30 AM , , Leave a Comment

This problem will sound broad and non-specific, but I've tried many things and don't know where else to turn.

I have an Excel VBA project - it has hundreds of lines of code and several modules so I can't really post it all. It works as a template - users open the file, make a bunch of changes to the template and save a copy somewhere. The master template never changes.

Everything works great, except it frequently crashes when the user closes Excel saying "Excel has stopped working". It crashes any opened excel files so pretty frustrating.

This error only happens when the file is opened from a shared network location. I've never once experienced this working locally.

There is no VBA code executing .BeforeClose that could be interfering. On workbook open, I have it set to fetch two different CSV files and copy data over from them.

I've gone through each module and sub to see if any of them are contributing to the crash - but sometimes the workbook may crash upon closing without having run any VBA code (outside of the Workbook.Open code)

Also, it will never crash when in 'Read Only' mode (the Master file is always read only) - only once the user has saved a copy somewhere will the crashing begin.

Does anyone have any tips or ideas on where I can further troubleshoot this issue? Been working on this one forever, about to hire an expert if I can't solve soon. Thank you!

**edit 4/15 - just an update, still searching for answer if anyone has solution. The only thing I can find related to the problem is referencing files on a shared network drive. Upon closing, is there a reason anyone can think of that may cause a crash?

4 Answers

Answers 1

Hard to tell for this type of issue. I think it's a case of trial and error for you.

What I would try initially, would be some of the following.

  1. Try a code cleaner on your Excel workbook. This one for example http://www.appspro.com/Utilities/CodeCleaner.htm
  2. I'd consider rebuilding the workbook from scratch if possible and recopying your code back in. I'd save the code to text files and remake the modules, classes, sheets from scratch.
  3. I'd clean out your temp directory. Excel saves VBA objects to the temp directory. If it gets too large it causes issues.
  4. I'd check there's no addins that potentially could be affecting things. Try removing any unused adding for a minimum build.
  5. In the past, I've had issues with some libraries that were early bound, but this was more with opening workbooks that caused exceptions. Perhaps you could try removing the references and late bind your objects - assuming of course you're not using events in those objects. So instead of using Dim x as SomeObject, use CreateObject("....") and remove the references to the libraries.
  6. I'd also have a look in the Microsoft Event viewer to see if you can get any more information. A specific exception may help you in the right direction.

Sorry these are a bit vague, but at least you can try them. It's hard being able to give more concrete recommendations remotely without being able to see what's happening / test out some possible theories.

Hope that helps.

Answers 2

Open a new instance of Excel, navigate to your file, click once, click the down arrow on the Open button, click Open and Repair. Does that help?

Answers 3

One reason could be due to AutoRecover. When AutoRecover is saving a backup copy and user also try to save, there is a very high chance that Excel will crash for a file on network path. AutoRecover by default runs every 10 minutes, which is also quite frequent. I'm not particularly sure why it crashes, could be due to network latency.

AutoRecover can be turned of programmably. But it will be turned off at application level instead of workbook level. So remember to turn it back on when exit the workbook you don't want to use AutoReceover with.

Hope this helps.

Answers 4

I am using Excels with Share drive too in my organisation... There can be many reason and depends on share drive you are using. If you are using shared drive which is web based and not compatible with Macros it will stuck excel. It is basically issue with office share point.

Also you need to shorter the code and need to use Option explicit in VBA code and describe all variables. Sometime due to missing defined variables it will stop working and excel will stuck.

Also you need to disable ADD-INS in excel to make it work faster. If you can share code people can tell better why it is not working perfectly. If you can share what type of Share drive you are using we can check that also because i faced same thing in past and i worked on my code and share drive again.

If You Enjoyed This, Take 5 Seconds To Share It

0 comments:

Post a Comment