Excel 2013 is very slow in loading large workbooks, is there a way to open just the tab I want?

Excel 2013 is extremely slow in opening my large workbook. Sometimes it won't respond at all and freezes my computer.
 

Question Info


Last updated February 20, 2019 Views 27,139 Applies to:

* Please try a lower page number.

* Please enter only numbers.

* Please try a lower page number.

* Please enter only numbers.

Hi Everyone,

 

I've sent some private messages out to try to get some more information on this issue to collect some of these workbooks to better understand this issue.

 

Thanks for your information on this issue.

 

Bill

Did this solve your problem?

Sorry this didn't help.

Great! Thanks for marking this as the answer.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this response?

Thanks for your feedback.

Hi Jacques,

 

We are investigating, but it appears that the difference in protect performance in 2013 is due to some security measures the developers added in 2013.

 

Regards,

Anita

Did this solve your problem?

Sorry this didn't help.

Great! Thanks for marking this as the answer.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this response?

Thanks for your feedback.

I've sent some private messages out to try to get some more information on this issue to collect some of these workbooks to better understand this issue. 

Bill


Please explain me what I have to do to send you my worbook also.

 

I already explained that I have exactly the same problem when I put some code to protect my worksheets an my workbook in the Workbook_Open event of ThisWorkbook.

 

After removing that code, the problem is gone and the workbook opens instantly.

 

Best regards

 

Jacques

Did this solve your problem?

Sorry this didn't help.

Great! Thanks for marking this as the answer.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this response?

Thanks for your feedback.

Hello

 

Is there any news about resolving this problem obviously caused when opening a Workbook where this Workbook and/or its Worksheets are protected via VBA code?

 

When this Workbook and/or Worksheets are protected manually, i.e. without VBA Code but using the possibility inside the Excel Worksheet menu, I have noticed there's no problem opening the Workbook, which opens within a normal lenght of time.

 

Best regards

 

Jacques 

Did this solve your problem?

Sorry this didn't help.

Great! Thanks for marking this as the answer.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this response?

Thanks for your feedback.

Hello everyone,

 

I thought that I had posted the results, but it looks like I have not. We have looked at the source code on this and it is, unfortunately, the result of some security changes in protection, and quite by design. So there was nothing I could do to get a change. The development team is very conscientious about not reducing any security measures we have in place. You can see the known issues list for confirmation: http://office.microsoft.com/en-us/access-help/office-2013-known-issues-HA102919019.aspx?CTT=5&origin=HA102901439#_Excel_(release)

 

Thanks,

Anita

Did this solve your problem?

Sorry this didn't help.

Great! Thanks for marking this as the answer.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this response?

Thanks for your feedback.

Thank you, Anita

 

But as far as I could see (or I am missing something) that known issues list does not contain the problem of the slow loading of large workbooks protected via VBA code.

 

Anyway I understood the problem cannot be resolved for security reasons.

 

I think there's only one workaround: protect the worksheets manually and the workbook itself via VBA code (but this way the worksheet protection is not as good as via VBA code I think).

 

So, I'm hoping that somewhere in the future it will be possible to have security measures not affecting the loading speed of our workbooks and worksheets.

 

Best regards

 

Jacques

Did this solve your problem?

Sorry this didn't help.

Great! Thanks for marking this as the answer.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this response?

Thanks for your feedback.

Hi Jaques,

 

I'm sorry if I wasn't clear. We don't have a repro on slow opens. If you can help us get that, I would really appreciate it. You say that if your files are protected, it takes a long time to open them? I will try protecting 185 sheets and then see about opening the files and time the difference.

 

I thought you meant that you were protecting the sheets when you opened the books.  We have a repro on the slow times it takes to protect the files programmatically. That's what we debugged to find the root cause.

 

It could be that we have to check the hash when we open the files, but I'm just not sure. I'll check on that

 

As far as the strength of protection goes, it's exactly the same whether you protect manually or programmatically.

 

Regards,

Anita

 

Did this solve your problem?

Sorry this didn't help.

Great! Thanks for marking this as the answer.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this response?

Thanks for your feedback.

Anita

 

The problem of slow opening a (large) workbook is indeed happening when this workbook and its worksheets are protected programmatically (VBA code), but not when the protection was made manually I noticed.

 

Already I wrote this in my post of February 5, 2013 above:

 

"In 'ThisWorkbook', 'Workbook_Open' event, I had a reference to a module 'ProtectWorksheets' which password protects all sheets and the workbook itself when loading the workbook.

 

I have 'commented out' this reference and now the file is loading normally.

 

Of course the question remains why that reference to 'ProtectWorksheets' didn't cause any problem in Excel 2007 nor in Excel 2010."

 

So the problem of the slow opening seems indeed to be caused by the protection made programmatically, because when I eliminate the programmatic protection, the Workbook opens within a normal time and even when I had protected worksheets manually.

 

For my Workbook with 16 worksheets (protected programmatically) it takes 10 seconds after the splash screen before the first worksheet becomes available. During those 10 seconds nothing can be done (frozen) except wait until it becomes available. The little "circle" (hour glass) keeps turning and turning. As to me I think the program is just busy executing the VBA code to protect the 16 worksheets or something like that. In former versions of Office this did not happen. Notice that during those 10 seconds the Workbook is opened in Excel on the first worksheet, but nothing can be done.

 

Best regards

 

Jacques

Did this solve your problem?

Sorry this didn't help.

Great! Thanks for marking this as the answer.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this response?

Thanks for your feedback.

Hi Jacque,

 

That's what I thought. You ARE protecting during the file open code. The extra time is not in open it's in the protecting itself. I protected 185 sheets programmatically, and then saved and closed the book. It opens quite fast. It does not take a long time to open a book with all the sheets protected.

 

The protection code itself took a long time. The reason is the stronger security in the protection, which is the same whether you do it programmatically or manually, but it isn't noticeable when you protect one sheet at a time manually. Protecting many of them programmatically compounds the problem. We have taken it to the developers already, and they say that they added the strong security to make sheet protection stronger, and will not remove it or give us a way to opt out of it because to do that would reduce that protection. I really cannot share the exact details of the changes.

 

If you can protect all the sheets, save them in the protected state, and take out the ProtectWorksheets code, the file should open fast. Then if you only unprotect the ones you need to, as you need to, you should not notice a huge perf issue. That is, you should not as long as you don't have to unprotect and reprotect a bunch of them at one time.

 

If anyone has workbooks that take much longer to open than they did in 2010, but do not have protection code running, it would be great if you could open a support case or get them to us somehow.

 

Thanks,

Anita

 

Did this solve your problem?

Sorry this didn't help.

Great! Thanks for marking this as the answer.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this response?

Thanks for your feedback.

1) That's what I thought. You ARE protecting during the file open code.

 

2) If you can protect all the sheets, save them in the protected state, and take out the ProtectWorksheets code, the file should open fast.   


1)   Yes, you are right about that.

 

2)  You are right about that also. I protected programmatically, saved and commented out the reference to the ProtectWorksheets code. The file opens instantly.

However, there's one big inconvenience: I get many "Error 1004 during execution" messages after clicking command buttons: "The cell or chart you are trying to change is on a protected worksheet."

I didn't get those errors when protection was created during the file open code, due to "UserInterfaceOnly:=True" included in the protection code. Unfortunately this is not working anymore after saving the sheets in the protection state and taking out the protection code. To have UserinterfaceOnly:=True working obviously one has to protect the worksheets each time during the file open. Or do you know another way to achieve the same result, i.e. to avoid those "Error 1004" messages? 

 

Best regards

 

Jacques

Did this solve your problem?

Sorry this didn't help.

Great! Thanks for marking this as the answer.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this response?

Thanks for your feedback.

* Please try a lower page number.

* Please enter only numbers.

* Please try a lower page number.

* Please enter only numbers.