Solved

Unreadable content errors

Posted on 2014-09-24
7
123 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
Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

 

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

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.

Join & Write a Comment

Introduction This Article is a follow-up to my Mappit! Addin Article (http://www.experts-exchange.com/A_2613.html), it was inspired by an email posting I made to EUSPRIG (http://www.eusprig.org/index.htm), I will briefly cover: 1) An overvie…
Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

757 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

21 Experts available now in Live!

Get 1:1 Help Now