Solved

Excel found unreadable content... after Picture is too large and need to be truncated

Posted on 2013-11-26
11
427 Views
Last Modified: 2014-02-05
Hello Experts, I have a very large Workbook consisting of 31 sheets, 16.000 lines of code in 10 modules, 38 userforms for UI and over 2,000 range names.
 It has been working perfectly for over 4 years, then few weeks ago I started having big problems when opening it. I get the infamous message: Excel found unreadable content... and 80% of the formulas are turned into values when I trust it and open it.
I've noticed that most of the times when it corrupts I get the "Picture is to large and needs to be truncated" message upon closing it.
Of course I have backups and automatic procedures for importing all data from the old file to the new one, therefore the issue is not vital but still it worries me quite a bit.
Is there a way to find out what is corrupting the file? Can I solve the Picture is too large... error when closing?
Any help would be deeply appreciated
0
Comment
Question by:yuppydu
  • 6
  • 5
11 Comments
 
LVL 7

Expert Comment

by:jaynee
ID: 39684650
Which version of Excel?  What changes have been made to the computer you are running this on just prior to the problem starting?

This seems to be an error being reported when copy-and-paste code is being run by 2007 excel code.  Try the section ‘Delete items from the Office clipboard’ in the link below

http://office.microsoft.com/en-us/word/HA101636021033.aspx

If that doesn't work, the poster below fixed his occcurence of this problem by turning off
his clipboard enhancement and memory optimizer applications.

http://answers.microsoft.com/en-us/office/forum/office_2007-excel/ms-excel-2007-error-message-the-picture-is-too/f9932485-43b7-4f73-af82-1f6f1f559438
0
 
LVL 4

Author Comment

by:yuppydu
ID: 39684731
Thanks much for the answer, I'll try what you suggest to get rid of the "Picture is too large and needs to be truncated", at least, if it works one issue is solved.

I am usin Excel 2010 and the code has been totally re-written on 2010. There is no clear pattern of corruption and I have not been able to replicate it. I have noticed that few time it occurs after the infamous "Picture is too large and needs to be truncated" although that is not a certainty that the workbook will break.
The strange thing is that when the files becomes unreadable over 80% of the formulas are converted into values.

Problem is that I have a lot of code, a lot of formulas and some external links.... it's quite complicated. I would love to know if there is some kind of Excel checker which scans for issues, but I guess there is no such a thing.

I'll keep the post open for a while and I'll let you know.
0
 
LVL 7

Expert Comment

by:jaynee
ID: 39684782
Oh dear - I'll wish you Good luck, cos rewritten code means that could be a very hard one to track down!

Other diagnostics to try would be opening copies of exactly the same file locally on different machines with different amounts of RAM available, if you can manage it.

I do think it's the paste special command misbehaving - that's one of the ways that you can convert formulas into values in Excel, doing a highlight, Ctrl-C then right-click and paste values.  I wonder if the problem is happening when the converted formulas are highlighted?
0
 
LVL 7

Expert Comment

by:jaynee
ID: 39684785
Oh - may be worth running thru this checklist:
http://support.microsoft.com/kb/271513
0
 
LVL 4

Author Comment

by:yuppydu
ID: 39684878
Jaynee very interesting article but I never receive error messages when saving the workbook. Everything seems to go smoothly. It is when I open the file that sometimes I get the odious message of Excel found unreadable content....

My auto_close macro is quite straight forward

Public Sub Auto_Close()

''' We can't allow errors during shut down, so this entire procedure is wrapped in On Error Resume Next.
    On Error Resume Next

    With wksNav.Range(gszRNG_NAV_START).End(xlDown)
        If Int(.Value) <> Date Then
            If MsgBox(gszMSG_ASK_SAVE_PORTFOLIO, vbExclamation + vbYesNo, gszMsgBoxTitle) = vbYes Then
                .Offset(1, 0).Value = Now
                .Offset(1, 1).Value = wksAssetAllocation.Range(gszRNG_PORTFOLIO_VALUE).Value
                ThisWorkbook.Save
            End If
        End If
    End With

    ''' Destroy all global object variables on exit.
    Set gobjBlpData = Nothing

End Sub


I'm starting to have the feeling that this is not debuggable  :-(
0
6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

 
LVL 7

Accepted Solution

by:
jaynee earned 500 total points
ID: 39684894
Errk, yeeeessss.... maybe take a test copy, comment out the On Error Resume Next so you can at least see if there are errors happening during the save.

I understand that the message happens when you open, but I'm betting the error happens either when it's closed/saved, or when the paste special is invoked.  Unfortunately, that means that you'd have to find every instance of the paste special command in the code, set breakpoints just before them and watches (for example some of the cells containing formulas that are getting written as values), then just step thru them until you see something hinky.  Just my 2c worth.
0
 
LVL 4

Author Comment

by:yuppydu
ID: 39689399
Thanks jaynee I'll probably remove the On Error Resume Next on one copy of the workbook and see if I ever get an error while saving. It must happen during saving.

BTW I never invoke paste a paste special value in my code. When I say that I get formulas overwritten by values it happens when I force the opening of the fille after excel tells me it found unreadable content in the file. At that point my file is gone!!
0
 
LVL 4

Author Closing Comment

by:yuppydu
ID: 39835603
Sorry for disappearing, I moved the file from a shared drive and saved it locally. That made the workbook a lot more stable and it has been working fine for a while. It can be that the issue is related to the shared drive and not to the file itself.
Unfortunately the doubt remains as the error cannot be replicated programmatically.
Thanks for the support
0
 
LVL 7

Expert Comment

by:jaynee
ID: 39836895
Thanks for the points, yuppydu. Good that you've found a way to make it more stable... permissions on the shared drive maybe involved?

Just one of those"chalk it up to cosmic rays" problems...
0
 
LVL 4

Author Comment

by:yuppydu
ID: 39837097
Well after over 10.000 lines of code on an 18 sheets workbook I think I have seen everything that can go wrong. I think the issue is a faulty hard drive sector on machine I use as a share drive and virtual server, but I did not have the time to scan the disk. Anyway now that is saved on a different drive locally it seems to work as usual.
It was a serious issue for me as I use the spreadsheet as a calculation benchmark for the software we develop.
Nice meeting you here, have a good day
Yuppydu
0
 
LVL 7

Expert Comment

by:jaynee
ID: 39837245
You too.
0

Featured Post

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
My experience with Windows 10 over a one year period and suggestions for smooth operation
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

743 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now