?
Solved

Large Excel 2010 file works badly in Excel 2007

Posted on 2012-09-11
41
Medium Priority
?
812 Views
Last Modified: 2012-09-28
Hi,

A client has sent me a large worksheet created in Excel 2010. It is an Excel Macro Enabled Worksheet (.xlsm) and it is 1.25 Mb.

When opened in Excel 2010 it works fine.

When openid in Excel 2007 (even on a pretty powerful PC) it takes several minutes to open, and then when opened it is too slow to be usable.

How can I improve this situation?

Thanks,

Richard
0
Comment
Question by:rpm
  • 14
  • 10
  • 7
  • +1
33 Comments
 
LVL 10

Expert Comment

by:etech0
ID: 38387530
Do you need the macros? If not, you can try saving it as an xlsx and see if that helps.
0
 
LVL 1

Author Comment

by:rpm
ID: 38387546
I have just tried doing this, and the performance does not appear to be improved.
0
 
LVL 11

Expert Comment

by:ScriptAddict
ID: 38387551
Difficult to say without more information.  

Does it access any external files or links or websites?

If you turn off calculations does it go slow?

Is it posting any errors from VBA code that work for 2010 but error out in 2007?  Have these been suppressed by an on error resume next?  

Hard to say without looking at the file.

If you run a short bit of vba code turning off events does that speed things up?

If it is looking for external links it can't find that will grind it to a halt.

-SA
0
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
LVL 10

Expert Comment

by:etech0
ID: 38387562
You can also upload the file (without confidential information) for us to take a look.
0
 
LVL 1

Author Comment

by:rpm
ID: 38387568
* Does it access any external files or links or websites?

I don't know, but both the 2007 and the 2010 machines are on the same network, so I suspect that the access would be the same for both.

* If you turn off calculations does it go slow?

How do I do this?

* Is it posting any errors from VBA code that work for 2010 but error out in 2007?  Have these been suppressed by an on error resume next?  

How do I check this?

* If you run a short bit of vba code turning off events does that speed things up?

How do I do this?
0
 
LVL 1

Author Comment

by:rpm
ID: 38387575
I'll have to seek permission to send you this file, as it has a lot of information in it. I will ask the client.
0
 
LVL 10

Expert Comment

by:etech0
ID: 38387576
To turn off calculations:
click Formulas
click Calculation Options
click Manual

It sounds like the code is not a problem, if saving it as an xlsx does not help.

Also try this:

Click Data, Connections

See if there is anything in the list, and let us know.
0
 
LVL 11

Expert Comment

by:ScriptAddict
ID: 38387635
Just use the Excel 2010 machine :)



* Does it access any external files or links or websites?

I don't know, but both the 2007 and the 2010 machines are on the same network, so I suspect that the access would be the same for both.

Then you might want to make sure that they have the same trust center macro settings.  
file>>Trustcenter>Trust Center Settings> Macro settings  

I'd pay special attention to the trust VBA object model checkmark.


* If you turn off calculations does it go slow?
file>Options>Formulas>workbook calculations manual (by presing F9)  You'll want to change this back after testing.  Although I think it does save the setting by workbook.

How do I do this?

* Is it posting any errors from VBA code that work for 2010 but error out in 2007?  Have these been suppressed by an on error resume next?  

Open the developer ribbon  (you'll have to turn  this one on using customise)  And then go into the vba editor and look at the code.  If you put a ' in front of on error resume next you'll see any errors that are occuring.  Some errors are designed to occur, so if you aren't familiar with VBA this might not help much.

How do I check this?

* If you run a short bit of vba code turning off events does that speed things up?

Again,  if you aren't familiar with VBA this might not be a good option for you.


'  the marker at the beginning of this line indicates a comment  by changing the commented line below you can toggle if  this code will turn events on or off.  This will deactivate any VBA Event triggers that might be used in the workbook.

Sub NoEvents()
Application.EnableEvents = False
'Application.EnableEvents = True
End Sub

How do I do this?
0
 
LVL 1

Author Comment

by:rpm
ID: 38387771
There are no links in the connections window.

Setting the calculation option to manual does not appear to help.
0
 
LVL 10

Expert Comment

by:etech0
ID: 38388029
Try copying all the data in the worksheet (or sheets) into a new excel file.
0
 
LVL 10

Expert Comment

by:etech0
ID: 38388036
(you can do this by selecting all the cells (control-a), and then doing control-c to copy, control-n to open a new file, and control-v to paste.
0
 
LVL 1

Author Comment

by:rpm
ID: 38388103
When I do the copy and paste into the new spreadsheet it tells me that some formulas will be pasted as values.

The pasted spreadsheet looks very different, but contains the same data.

I do not know enough about the operation of the sheet to work out what does or doesn't work, but the performance is fine on 2007.

Any idea where we go from here?
0
 
LVL 10

Expert Comment

by:etech0
ID: 38388122
In the original file (not the one you just created), click Data, and click Edit Links.

Is there anything in the list? If so, it's telling you that it's pulling in information from other workbooks.
0
 
LVL 1

Author Comment

by:rpm
ID: 38388379
I'm not at my desk now to check this, but I am guessing not as it was emailed to me in isolation, and it works fine on my Excel 2010 computer.
0
 
LVL 10

Expert Comment

by:etech0
ID: 38388405
It may be worth double checking.
0
 
LVL 26

Expert Comment

by:redmondb
ID: 38388724
Hi, Richard.

IIRC, there were two areas in particular which had speed issues in 2007 that Microsoft significantly improved in 2010 - Charts and Shapes. Does your file have either of these?

Regards,
Brian.
0
 
LVL 1

Author Comment

by:rpm
ID: 38390325
No to charts or shapes!
0
 
LVL 1

Author Comment

by:rpm
ID: 38390327
Edit Links is greyed out, so I assume that means there aren't any.
0
 
LVL 26

Expert Comment

by:redmondb
ID: 38390440
Thanks, rpm.

Could you post a redacted copy of the file here, please? Ideally the version should give the same problems, but even if it doesn't that will give us valuable leads.

Thanks,
Brian.
0
 
LVL 1

Author Comment

by:rpm
ID: 38405034
I have obtained permission to send out the redacted spreadsheet.

If any experts would like to look at the spreadsheet, please send an email to richard@domain-name.uk and I'll email the spreadsheet back.

Richard

**Email address removed per EE policies.**-JARmod101
0
 
LVL 1

Author Comment

by:rpm
ID: 38405037
(Note the hypen is part of my address, as the address has been split over 2 lines!)
0
 
LVL 26

Expert Comment

by:redmondb
ID: 38405382
Richard,

I got the file, thank you.

Good news - I seem to be seeing the same issues. The virtual PC I'm opening the file in is currently running at 100% cpu.

At first sight, your conditional formatting "Applies To" has a complicated range. I'll tidy that up and see if it makes a difference.

Regards,
Brian
0
 
LVL 26

Accepted Solution

by:
redmondb earned 0 total points
ID: 38405756
Richard,

OK, I removed all of the conditional formatting and the file seems fine now, so please check it out.

Couple of points...
(1) Presumably you'll want the conditional formatting put back. Please tell me the cells that are supposed to have it. Recreating them as a single range means that you want see the current problems.

(2) However, if the file is continually having cells added and deleted then the problem will recur. If that's the case then you need to amend your update macro (I assume) so that it starts by deleting the formatting, does its work and finally recreates the conditional formatting.

Regards,
Brian.

[Attached file removed by zorvek at 8:37 AM PST on 2012-09-17 per poster's request.]
0
 
LVL 1

Author Comment

by:rpm
ID: 38405843
That's great news, who'd have thought that conditional formatting rules could have such different effects in 2007 and 2010.
0
 
LVL 1

Author Comment

by:rpm
ID: 38405848
If possible, can you remove the attached sheet from you post now please.

Thanks,

Richard
0
 
LVL 26

Expert Comment

by:redmondb
ID: 38405909
Sorry, I didn't think. I'll request that immediately.
0
 
LVL 26

Expert Comment

by:redmondb
ID: 38406044
Thanks, Kevin.
0
 
LVL 1

Author Comment

by:rpm
ID: 38406048
Thanks!
0
 
LVL 26

Expert Comment

by:redmondb
ID: 38406441
JARmod101,

Once again my apologies about needing to have the file removed - that was as a result of a stupid oversight on my part, rather than a deliberate plan!

I agree completely about being fair to everyone, however I don't see that this infringed that - any expert who bothered to email Richard would have received a copy of the file.

Finally, even without the original or modified files, this PAQ is still useful for anyone who may have a similar problem.

Regards,
Brian.
0
 
LVL 26

Expert Comment

by:redmondb
ID: 38408597
Netminder,

I don't give a hoot about the points, but I do care about the fact that my mistake is causing such a fuss. Sincere apologies to all concerned.

Brian.
0
 
LVL 1

Author Comment

by:rpm
ID: 38408611
For what it is worth, should anyone be reading this question, the solution that Brian found was correct, and is well worth looking into if anyone comes across a similar problem.

The original spreadsheet turned oput to have literally pages of conditional formatting rules. In Excel 2010 this was not a problem and the sheet performed fine. In Excel 2007 the same file was unworkably slow.

Removing the conditional formatting rules (and replacing them with a simplified set) fixed the problem.

I hope this summary returns this question to a useful state, event without the spreadsheet itself!

My thanks to Brian for solving a problem that was causing me a lot of confusion, he is a real Excel expert!
0
 
LVL 26

Expert Comment

by:redmondb
ID: 38408640
Thanks, Richard.
0
 
LVL 26

Expert Comment

by:redmondb
ID: 38446320
zorvek,

There have been a number of emails between Netminder and myself and, as a result, I am content to let things remain as they are.

Many thanks for your support,
Brian.
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Lost Word File? Eagerly, need it back? Read ahead; this File Recovery guide is for you.
Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…

807 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question