Solved

Unreadable content errors

Posted on 2014-09-24
7
125 Views
Last Modified: 2014-10-06
I use this to save a file in a workbook, however after saving it, it opens declaring there are unreadable content errors, fix? Yes/No.  When I select Yes, another display, that we just close.  We then proceed to resave the file again under its original name and it is fine.

Is there a way to capture the filename and do the reopen, fix, save and close in a macro?
We are having issues figuring out just what is causing the unreadable content.  I was thinking if we can fix the file, we do not have to dig so far into this and why the content is bad...

'Save file to FName2
    Application.DisplayAlerts = False 'use if overwritting file to nix popup
    Dim FName2 As String
'   L-learned do not use lettered drives
    FName2 = "\\na\Global-Groups\Holland\Operations\Logistics\DSC\Go to Work Signals\RePlan Report\AM PM RePlan Report "
    FName2 = FName2 & Format(Date, "yyyy.mm.dd") & " " & Format(Time, "hhmm") & ".xlsx"

    ActiveWorkbook.SaveAs Filename:= _
        FName2, FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
    ActiveWorkbook.Close

Open in new window

0
Comment
Question by:RWayneH
  • 4
  • 3
7 Comments
 
LVL 4

Expert Comment

by:yuppydu
ID: 40343454
Normally on Auto_close subs, given that you can't allow errors during shut down,  I wrapp the entire procedure in On Error Resume Next command.

Try adding it at the beginning of your code.
I would advise to first save the file wit a different name before testing it, just in case something goes wrong.
0
 

Author Comment

by:RWayneH
ID: 40343824
The issue is not that it fails..  the file save process works fine.   The issue is when we goto open the result of the file save.  That is when the unreadable content errors are happening.  The work around is to open it, answer Yes to fix, the next popup click Close (only option).  Then file save and the same filename, close.  This removes whatever it was hanging onto that was causing the unreadable issue.

My request is...  Is there a way to use vba to perform those steps?  I was thinking about using Application.DisplayAlerts = False    But not sure how to capture exactly what name was given to the file that was saved to try that.  I was hoping that DisplayAlerts = False will allow the file to open.  So we can resave it as the same name/path.  Do you know how to determine the exact filename the code is giving it?
0
 
LVL 4

Expert Comment

by:yuppydu
ID: 40345578
Unreadable content in Excel is a nightmare to fix, based on my past experience.
I have no good news for you. The only times I have seen it the workbook in question was becoming corrupt. As time goes on you will lose more things and eventually the workbook becomes unusable. The only option is a clean rebuild of the workbook.

    If you decide to do this be very careful. The only thing you can copy between the old workbook and the new workbook are plain values (Copy then Paste Special > Values). I have seen corruption hiding in formulas, formats, VBA, the structure of worksheets and other places I probably don't even remember now, so anything other than plain values that is copied from the old to the new workbook can bring the corruption with it.

If you can post theworkbook I can try to narrow down what is causing, but no assurance of pinpointing the issue
0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 

Accepted Solution

by:
RWayneH earned 0 total points
ID: 40346411
I believe the corruption is when I remove sheet tabs out that have pivot tables in them and some how it is linked to the original sheet tab it was created from.  I am going to try some other things like instead of copying the sheets out, just leave them there and hide the unneeded ones.  Hoping this will correct the issue.  Issue is not in the master workbook, it is in the workbook that is created from it.  I am hope a rebuild is not needed... it is a pretty involved process.  Not going to attach it and put you through that.  Thanks for the tips on the rebuild...   that will be the last resort.
0
 
LVL 4

Expert Comment

by:yuppydu
ID: 40346452
Without knowing the exact workbook architecture it is difficult to give you any better advise. Of course rebuild is always the last resort. Good luck with it.
0
 

Author Closing Comment

by:RWayneH
ID: 40363241
Thanks for the comments.
0
 
LVL 4

Expert Comment

by:yuppydu
ID: 40363803
First time I see an author of a question assign points to itself. Firstly I do not get why he asked for help before doing his homework, secondly it does not make sense.
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Sparklines have been introduced with Excel 2010 and are a useful tool for creating small in-cell charts, used for example in dashboards. Excel 2010 offers three different types of Sparklines: Line, Column and Win/Loss. What it does not offer is a…
Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

932 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