?
Solved

Excel 2007 spreadsheet hangs

Posted on 2011-10-23
19
Medium Priority
?
357 Views
Last Modified: 2012-05-12
Workbook has multiple linked spreadsheets and is 394MB.  I added a spreadsheet to copy two columns of data from each of 9 spreadsheets onto one spreadsheet.  The columns I am copying have merged cells so the data is only in every other cell.  I created the formula on a temporary spreadsheet and then copied and pasted it onto the actual spreadsheet.  I then used Find and Replace on the temporary spreadsheet to update the formula for the next spreadsheet. Then copied and pasted it onto the actual spreadsheet. All was well for the first 7 updates, however when I tried to paste the updated formula for spreadsheet 8 the program hung.  I have tried the Open and Repair to no avail.  Attached is the screen shot from the Event viewer.
Event-Properties.jpg
0
Comment
Question by:Lindahq
  • 10
  • 6
  • 3
19 Comments
 
LVL 26

Expert Comment

by:redmondb
ID: 37015542
Lindahq,

It's not clear to me exactly what you were doing.

(1) You mention a very large workbook and then talk about a number of spreadsheets. Are these worksheets within the very large workbook or are they other workbooks?
(2) You mention an Open and Repair attempt. Did you run this simply because of the failed run or did you get an error/hang opening a workbook? (If so, what was the error and which workbook?
(3) Did you reboot after the Excel hang before attempting to re-open the workbook? (I ask this because it's very problem you were having memory issues and following the hang Windows may not have released all of the memory leaving insufficient to re-open the workbook.)
(4) You're running 32bit Excel 2007, correct? What version of Windows? What type of Excel file? How much memory on your PC? Were any other programs open at the time?
(5) 394MB is a lot of spreadsheet! Why does it need to be so big? Any possibility that you could move at least some of it to, say, Access?

Thanks,
Brian.
0
 
LVL 26

Expert Comment

by:redmondb
ID: 37015559
Apologies, point (3) went off the rails. It should read...

(3) Did you reboot before attempting to re-open the workbook? (I ask this because it's very possible that you were having memory issues. Following the hang, Windows may not have released all of the used memory thus leaving insufficient to re-open the workbook.)
0
 

Author Comment

by:Lindahq
ID: 37015902
I am on Windows XP Pro.

The workbook opens without any problems.  It hangs when I try to edit one worksheet in the workbook.

I did the repair because of the hang.

I did a hard reboot after the repair.  

All of the spreadsheets are in the same workbook.

Unfortunately no portion of the workbook can be moved to Access as others use the workbook and they are not Access users.

I can send the file if that would help.
0
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.

 

Author Comment

by:Lindahq
ID: 37015909
Forgot to add...2GB RAM Intel Core Duo CPU T2350 @ 1.86 GHz
120 GB HDD with 27 GB free
0
 
LVL 26

Expert Comment

by:redmondb
ID: 37016263
Lindahq,

Thanks for all that info.

It hangs when I try to edit one worksheet. As a test, can you delete all (or at least some) of the other sheets and then try to update the "bad" sheet?

You mention "other users" - is this a shared workbook (i.e. allowing multiple simultaneous users) ?

Regards,
Brian.

0
 
LVL 26

Expert Comment

by:redmondb
ID: 37017257
Lindahq,

You offered above to upload your file. Assuming that there's nothing confidential in it, I think this would be a good idea. The file is way too big for Experts Exchange, but you could use one of the free sites (DropBox, SkyDrive, Google Docs etc.) and post the link here.

Thanks,
Brian.
0
 
LVL 11

Expert Comment

by:ScriptAddict
ID: 37018510
Linda,
I know it's not optimal if the data is refreshed and you constantly need this info, but I find after sheet one is there if you copy paste values, it will dramatically reduce the size of the file.  If you made it 7 sheets before you could complete the project, at least as a snapshot, you could finish all 9 sheets if you copy paste values before moving onto the next sheet.

-SA
0
 

Author Comment

by:Lindahq
ID: 37018592
I just noticed a typo in my original post.  The file size is 3.94 MB not 394 MB!

Prior to reading your last response, I deleted the offending page, saved the workbook without a hang occurring, and closed the workbook.  I then opened the workbook, and recreated the worksheet.  This time I was able to save the worksheet, BUT with each addition of new section of formulas the paste time and workbook save time increased.  The final paste took over 30 minutes to complete and the save of the workbook took 2 hours.  

I am attaching the workbook.  The offending worksheet is titled "Placing." I was copying the 41 rows of formula from the worksheet labeled "Sheet 1" into sections on Placing.
2011-MASTER-after-changes.xlsx
0
 
LVL 11

Expert Comment

by:ScriptAddict
ID: 37018623
It should be noted that Excel will never use more the 2GB of memory.  That said, if you can't get your file to work another way I would record a macro that would do the copy paste of the formula for you so it could be a one button update of the "snapshot"
0
 
LVL 26

Expert Comment

by:redmondb
ID: 37019519
Oops, not SkyDrive - it has an individual file limit way below your file's size.
0
 
LVL 26

Expert Comment

by:redmondb
ID: 37019545
Sorry, posted above without refreshing from my last post. Thanks for the file, I'll get on to it now.
0
 
LVL 11

Expert Comment

by:ScriptAddict
ID: 37019567
It doesn't sound like your doing anything wrong, or that there is something broken.  

Trust me, I do a lot of data mining in huge workbooks/databases in Excel, and when you get up there, exactly what your describing occurs.  

I've found the best solution if I can't deal with the slowdown is to just copy paste values to reduce the number of references.  If one or more of the files linked to is in a closed Excel workbook, opening the workbook it is linked to can also reduce the stress on Excel.

Unless you are having problems with other workbooks, I'd say everything is doing what it should.  Your just hitting upper limits of what Excel can do effectively.
0
 
LVL 26

Expert Comment

by:redmondb
ID: 37020405
Lindahq,

Good news - the problem with this spreadsheet is that there are 160,000+ drawing objects on the Placings sheet.

Bad news - I've spent the last while trying to delete them. Unfortunately everything I've tried leaves the PC running one of its CPU's at 100%. I've canceled each attempt after roughly 10 minutes.

From what you've said, I may simply be too impatient, but I'll try a couple more approaches before I simply let a straightforward delete run for a couple of hours.

Regards,
Brian.
0
 
LVL 26

Expert Comment

by:redmondb
ID: 37020655
Linda,

Better news - the attached has had the drawing objects removed and is vastly more manageable. I made no other changes. Please give it a go and let me know how it behaves.

Regards,
Brian.
2011-MASTER-after-changes---Clea.xlsx
0
 

Author Comment

by:Lindahq
ID: 37020877
OMG what a diference!!! It is now a normal spreadsheet.  That explains why the fiel size became so inflated after I added that sheet.

I have no idea how drawing objects would have gotten into that spreadsheet.  It was a new blank spreadsheet that I simply entered formulas into (either manualy or by using copy/paste).

How did you determine the spreadsheet contained drawing objects?

You TOTALLY ROCK!!!!
0
 
LVL 26

Accepted Solution

by:
redmondb earned 2000 total points
ID: 37021336
Thanks, Linda.

A .xlsx (or .xlsm) file is really a zip file. I looked inside it to see if there was anything unusual. There was file in there (drawing1.xml) which had an unzipped size of 150MB - coincidentally, your original misprint wasn't that far out after all!

I finally got the file under control by editing that file to remove almost all of its entries and rebuilt the .xlsx. Then it was easy to write a macro to run through the sheets deleting all remaining objects.

A few other points...
(1) A couple of other sheets also had lots of drawing objects
      Sheet1 - 18,033
      Reg PT -      399
(2) As you don't know where these mystery objects came from, there's the worry that they may come back again. The crude macro below will give you a message about the location and number of diagrams in the current workbook. It obviously doesn't fix the problem but at least it lets you know if the objects start to build up again.
Sub Drawing_Objects()
Dim xSheet As Worksheet
Dim xhold As String
Dim i As Long

For Each xSheet In ActiveWorkbook.Sheets
    If xSheet.DrawingObjects.Count > 0 Then
        xhold = xhold & Chr(9) & xSheet.Name & " - " & xSheet.DrawingObjects.Count & Chr(10)
        i = i + xSheet.DrawingObjects.Count
    End If
Next

If i > 0 Then
    Call MsgBox(Format(i, "#,##0") & " Drawing Object(s) found..." & Chr(10) & xhold, , "Checking " & ActiveWorkbook.Name & "...")
Else
   Call MsgBox("No Drawing Objects found.", , "Checking " & ActiveWorkbook.Name & "...")
End If

End Sub

Open in new window

Regards,
Brian.
0
 

Author Comment

by:Lindahq
ID: 37021508
After I received your message about the drawing objects I deleted the other two worksheets you mentioned.  I too was afraid they would have the same issue.  Actually Sheet1 was just a temporary place to update the formulas before I copied and pasted those into Placing.  RegPt was a copy of Placing that was intended for another use.  So after I deleted it all I had to do was make a copy of the worksheet you fixed.

Thank you so VERY much for the macro.  I have saved it in a very special folder, but hope I never have to use it.

I wish I could give you more than 500 points.  You truly deserve more.  Plus you saved my sanity!!  You assitance was worth far more than what I pay to be a member!  

THANK YOU!!!

PS
Do you ever sleep?<LOL>
0
 

Author Closing Comment

by:Lindahq
ID: 37021511
Brian's resolution truly deserved more than 500 pts!!  He should be dubbed Excel Lifeguard because he saved my Excel life!
0
 
LVL 26

Expert Comment

by:redmondb
ID: 37021567
Linda,

Besides the pleasure from your kind words, I get a great kick from killing such an impactful problem - from the little time I spent with the file, I got some idea of the frustration it must have been causing you. Finally, I got to learn more about the internals of spreadsheets.

I should be giving you points!

Many thanks,
Brian.
0

Featured Post

[Webinar] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

Question has a verified solution.

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

Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

571 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