Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 134
  • Last Modified:

Unreadable content errors

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
RWayneH
Asked:
RWayneH
  • 4
  • 3
1 Solution
 
yuppyduCommented:
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
 
RWayneHAuthor Commented:
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
 
yuppyduCommented:
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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
RWayneHAuthor Commented:
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
 
yuppyduCommented:
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
 
RWayneHAuthor Commented:
Thanks for the comments.
0
 
yuppyduCommented:
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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now