Microsoft Excel - exhell 2007 corrupts an xls when it opens it ("Unreadable Content..")

oy.

I have an xlsm which opens source and destination .xls files and uses copy-destination to move a page from one to the other, as follows:

Set xlWB_dest = Workbooks.Open("blah")
Set xlWB_src = Workbooks.Open("yadda")

With xlWB_src.Sheets(1)

  .UsedRange.Copy Destination:=xlWB_dest.Worksheets(1).Range("A1")

End With

xlWB_dest.SaveAs xlWB_dest.Name, xlExcel8

Application.CutCopyMode = False

xlWB_src.Close

xlWB_dest.Close

xlWB.Close

However when an attempt is made to run the process a second time, it seems that xlWB_dest has been corrupted, and references made to it from the xlsm are now invalid.  I've pulled out enough hair on this.  It's time to call for reinforcements.

I did try running the same procedure from a workbook that wasn't an xlsm, but was an xls, and the same error occured.

And just to make it more fun, it's intermittent.

Thanks in advance.

Hi chaz,

Try this code

• Sub moveSh()
  Workbooks.Open Filename:=ActiveWorkbook.Path & "\" & "blah.xls"
  Set xlWB_dest = ActiveWorkbook
  Workbooks.Open Filename:=ActiveWorkbook.Path & "\" & "yadda.xlsx"
  Set xlWB_src = ActiveWorkbook
  With xlWB_src.Sheets(1)
    .UsedRange.Copy Destination:=xlWB_dest.Worksheets(1).Range("A1")
  End With
  xlWB_dest.SaveAs xlWB_dest.Name, FileFormat:=xlExcel8
  Application.CutCopyMode = False
  xlWB_src.Close
  xlWB_dest.Close
  Application.Quit
  End Sub
• Attachment -->Sample Folder.zip

Hope this help.

Pichart Y.

Beauteous.  I extend a hopeful "thank you."  Rarely have I found a solution so clean and effective so fast. 

I left off .quit because I didn't want to .quit.  It seems to work on my machine. Will deploy later for a more strenuous test.  And if there's one thing Office 2007 has taught me, it's that, just because it works today doesn't mean it will a week from now.  But the fingers are crossed. 

You clearly are a Jedi.  An initiate into the dark arts of the planet Redmond [humble bow, but nothing too dramatic].

Praytell, Yoda: WTF?

This one had me in a very bad humor.  I paced the streets of the City at night, glowering.  Dogs whimpered when I passed by. 

Now then, whatever it is that causes/solves this is clearly the consequence of far too much complexity in E2007.  Is the syntax really that much different?  I was still copying the right stuff. It was still pasting in the right place.  It was just freaking out afterwards somehow.  Again, Yoda, WTF?


[edit, a mere 15 minutes or so later]

and lo, now it doesn't work.  I'm considering ceasing work on E2007.  I've never been a m@sochist (that word flagged the "inappropriate content" wizard!)

Excel used to be greatest thing since sliced bread.  Supported my family for years with it. Now I'm telling you it's driving this developer away.  I'm sure I won't be the last.  What a piece of junk it's become.  Maybe it's me. Maybe I'm just ignorantly scrambling references.  Would that it would be that simple and straightforward.

Hi Chaz,

I may be good at Excel, but not very good at English. English is not even my 2nd language....so sorry I did not understand all of what you have said above, so much english style.   :(

Anyway, I am glad that I can save some of your hair...today :)

nice to meet you.

Pichart Y.

Why are you doing the SaveAs on xlwb_dest?  I would think that it would retain its existing format...

Your code looks fine, I would expect it would work with the SaveAs, just seems unnecessary.

Why are you doing the SaveAs on xlwb_dest?  I would think that it would retain its existing format...

Good question.  It didn't work when I only did "save", so, in my ignorance, grasping for a lifeline, I reasoned (if you can call it that) -- based upon the very sparse and cryptic results of an attempted search on the error -- that perhaps 2007 was trying to do unnatural things to it and that I need to TELL it to save it as a 97-2003.

Anyway, it doesn't work, and the fix, as is so often the case, is to fuhgeddabout using Excel "functionality" and just find a way to do without.  Git-'R'-dun!  So I simply consolidated the workbooks. Now the book that calls the routines also has xlWB_dest within it -- fortunately I could get away with doing that in this case.  It's merely copying to itself, not saving an external book, and {so far} all is sweetness and light in that regard 

Did you see Pich's last post?  Fortunately he didn't get a word of what I said!  God bless him.

I wouldn't have guessed. 

(it is a 97-2003 format to begin with).

BTW, there's another workbook that I open, execute a resident subroutine on, and then close -- very clean and simple -- and now it's blowing up on the "Application.Save" line.  Again, I'll just find another way to do it -- so many hacks, so little time...

So now I'm wondering if you are having an Office application issue, rather than a VBA issue.  Have you tried running the code on another machine?

I do the Save/SaveAs on a regular basis, sometimes to a 07/10 format, sometimes to the 03 format, don't tend to have problems.

Pichart is good, and getting better.  I would not fare well in my second language, and don't know enough of a third to say I have a third.

Thx for your feedback.  Yes, I get the error on another machine -- and sometimes more errors.

Pich was great. He's still a Jedi.  I didn't try it but I think quitting the app might indeed avoid the issue -- it seems the instance gets "contaminated" by openeing the other workbook.  But as I mentioned to him, quitting it defeats the purpose.  His English is better than he thinks it is.

I have a hunch this is related to Office's new naziesque security regime but I don't know how.  A long time ago I had an issue with Access 07 corrupting a database it didn't recognize because of the existence of vba modules.  I think Pat Hartman and maybe you, too, talked me through that one.

But I've "trusted" the locations of the books in question. 

I've also attemtped opening separate instances of the excel object for each book but I can't find a way copy between them, and found no solution via a web search.

I did find this link about the problem, finally -- the first direct description of it -- but it was never resolved.  The last time I posted a link here the entire thread was pulled but hopefully that was because I did something wrong and this one will be ok.... http://www.pcreview.co.uk/forums/corrupt-excel-copy-paste-between-instances-t3763650.html

Maybe a work-around.  If you don't need to keep the formatting from the source workbook, and you only need values to transfer across:

dim lngRows      as long
dim lngCols      as long

with xlwb_src.sheets(1)
  lngrows = .usedrange.rows.count
  lngcols = .usedrange.cols.count
  xlwb_dest.worksheets(1).range("A1").resize(lngrows, lngcols).value = .Sheets(1).usedrange.value
end with

It if is related to styles, this should get around the issue because it will adopt the style of the destination workbook. 

With a bit more work, the formatting and/or formulas in the destination workbook could be updated as well.  More than one way to skin a cat.

Yeah, if you read on and follow some more links, it seems to indicate that there is a phenomenon known as "style corruption" which has to do with accumulation of styles upon copying and pasting from one wb to another. The cumulative nature of the problem would seem to explain its intermittent nature, and support the "remedy" of closing the instance altogether and reopening it.

Of course, this would be grossly unprofessional of MSFT to leave such a probem unaddressed, and that offends me.  Once I get over that, I might actually try to enter some code to deal with it, such as yours (thank you).  Here's another cool thing I found once I started searching for "style corruption" or something like that: social.msdn.microsoft.com/Forums/nl-BE/exceldev/thread/e59e9b8d-9cda-4385-9b06-712df76c9861 . See Pete Thornton's posts -- really seems to have a handle on it.

When I ran his code I only found about 50 or so styles, so I'm not convinced it's the problem...however, I will, given time, check the premise.  I do other work in Access where I format spreadsheets (I think we've talked about that before) where, among the issues I deal with, this is NOT included.

Always good to know of problems that crop up, and there always seems to be one more.  I've read Peter on other forums as well, he makes for a good read.

Thanks for digging that link out.

After nearly working for a day, the workbook is now throwing up the compatibility checker when attempting to save workbooks via code.  No matter what options are chosen, the main workbook is uselessly corrupted afterwards.

the ActiveWorkbook.CheckCompatibility = False option recommended on the MSFT website doesn't seem to solve the problem.

Turning displayalerts off seems to help (for now, at least).  But that hardly leaves me feeling confident about having solved the problem.

Friends and neighbors, this product is useless except in its most basic functionality. This isn't programming -- it's subservience to MSFT.

/whining

Here's the latest:

The Main workbook was (by legacy design) reaching out and opening a linked template book, copying/pasting as values its one sheet, and then saving it under a different name.  Just yesterday that save action began throwing the aforementioned compatibility errors and another warning about formulas not calculating right if references are made to cells that don't exist or something like that.

It finally dawned on me to convert the templates into xlsx's -- not because there is any good reason to, and not because anything, anywhere in either documentation or a catalog of good common sense suggests it -- but because I reasoned that, since versions are the problem, maybe I should simply try to reduce the versions so I can get on with my life.  And lo, it seems to cooperate -- so far.

However, not without another bizarre, counter-intuitive wrinkle devoid of anything I can consider sound logic. This is what I figured would make sense (the procedure existed -- all I did was change the extension to refer to the new xlsx {and add a futile call to DeleteStyles}):

ChDir "G:\blah\Template_v8"

    Workbooks.Open Filename:= _

    "G:\blah\Template_v8\ Template.xlsx"

    Cells.Select

    Selection.Copy

    Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _

    False, Transpose:=False

  Application.CutCopyMode = False

    Range("A1:K1").Select

    ChDir "G:\yadda"

   

  DeleteStyles

    ActiveWorkbook.SaveAs Filename:= _

    "G:\yadda\theFile.xlsx", _

    FileFormat:=xlNormal, Password:="", WriteResPassword:="", _

    ReadOnlyRecommended:=False, CreateBackup:=False

    ActiveWindow.Close

But that throws the "Excel unable to open because of unrecognized file extension" error.  THIS is what actually works (so far) -- note the minor (and bizarre) change in the destination extension from the example above -- from xlsx to xls:

ChDir "G:\blah\Template_v8"

    Workbooks.Open Filename:= _

    "G:\blah\Template_v8\ Template.xlsx"

    Cells.Select

    Selection.Copy

    Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _

    False, Transpose:=False

  Application.CutCopyMode = False

    Range("A1:K1").Select

    ChDir "G:\yadda"

   

  DeleteStyles

    ActiveWorkbook.SaveAs Filename:= _

    "G:\yadda\theFile.xls", _

    FileFormat:=xlNormal, Password:="", WriteResPassword:="", _

    ReadOnlyRecommended:=False, CreateBackup:=False

    ActiveWindow.Close

So is there any way to flag my own post as helpful?

I don't know how this slipped by for a couple weeks, but it did.

You might try specifying the xlExcel12 format, rather than xlNormal (or xlWorkbookNormal).  The reason I was checking this thread though, was to suggest that you open a new instance of Excel to open the new workbook.  This would alleviate the styles issue as well.  I see you've done that from MSAccess, it is essentially the same thing here.

    Dim xlApp     As Excel.Application
    Dim xlWB      As Excel.Workbook

    Set xlApp = CreateObject("excel.application")

    Set xlWB = xlApp.Workbooks.Open(Filename:="G:\blah\Template_v8\ Template.xlsx")
    xlWB.Worksheets(1).UsedRange.Copy
    xlWB.Worksheets(1).PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
    Application.CutCopyMode = False
    Range("A1:K1").Select
    ChDir "G:\yadda"
    xlWB.SaveAs Filename:="G:\yadda\theFile.xlsx", FileFormat:=xlExcel12, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, CreateBackup:=False

    xlWB.Close
    Set xlWB = Nothing
    xlApp.Quit
    Set xlApp = Nothing

Ron De Bruin has a good page at:
http://www.rondebruin.nl/saveas.htm