Office 2010 Breaks Backwards Compatibility With Certain VBA Projects

So it seems that Microsoft has gone ahead and “done us all a favor” by having Office 2010 now save any type libraries that have been referenced in the Visual Basic Editor with the document itself to further improve compatibility between different versions of Office. What this actually ends up doing is completely breaking our VBA macros and sometimes the entire document becomes unusable on any computer running a previous version of Office.

We developed the Excel file on a computer with Office 2007, making a reference to the “Microsoft Word 12.0 Object Library” because we wanted to send some data from the Excel file out to a Word document using VBA, something like a simple “Generate Report” button. This worked and we were able to open the Excel file on computers running both Office 2007 and Office 2010, as well as generate the report.

But here’s the problem…Once the Excel file was opened then saved using Excel 2010 – without having any changes to the VBA code – the “Microsoft Word 12.0 Object Library” reference that was specifically added disappears and gets automatically replaced with a “Microsoft Word 14.0 Object Library” reference. Now when we open the Excel file on a computer with Excel 2007 it will try (and fail) to load the Word 2010 (14.0) Object Library and this results in a few serious problems:

  1. Opening the file in Excel 2007 is very slow and causes the program to stop responding.
  2. Attempting to correct the reference through the Visual Basic Editor is a no-go. The reference appears as “MISSING: Microsoft Word 14.0 Object Library” with a check mark. Attempting to clear the check to remove the reference results in an error: “Can’t perform requested operation”. Microsoft says this error happens when doing something illegal during VBA code execution, but we are simply opening the file and trying to remove the reference – we are not in break mode and no code is being executed!

I understand what Office 2010 is trying to do in regards to embedding the type libraries with the document itself for backwards compatibility, but it just doesn’t seem to be working correctly. The 14.0 PIAs/libraries don’t actually get embedded, but the reference does which makes no sense.

We’ve altered our VBA code to use late binding instead of early binding which avoids this problem completely, but we still have those corrupt files laying around with which the only thing we can do is open them and watch Excel hang itself constantly as it tries to load the wrong library.

The only fix I can think of is opening the affected files on a computer with Excel 2010 to attempt removal of the “Microsoft Word 14.0 Object Library” reference, assuming it does not add itself back when saving the file. Perhaps installing the Office 2010 PIA’s on the computers with Office 2007 would grant access to the Word 2010 (14.0) Object Library, but that is a workaround if anything.

“If you save the VBA code in a newer version, users of older versions will have to remove the missing reference(s) and set a reference to their version again – a tedious job.” [1]

If only that tedious job actually worked…

References & Related Info

[1] Clarification on this issue from a Microsoft MVP

Related

Tags: , , ,

12 Comments

  1. Have you tried installing the microsoft office 2010 data connectivity components?

    Kind regards,

    Jo Jacobs

  2. Thanks very much for posting this. I just ran into exactly this problem, and reading this saved me a lot of bother trying to fix. I just opened the frazzled workbook, without enabling macros, and copied all the worksheets into a new workbook. Luckily I had an older version with all the VBA code, as I couldn’t work out how to recover this from the frazzled one (attempts to copy and paste brought up the error dialog).

    Good idea to use late binding as a workaround. Shame to lose intellisense though – know any way around that?

  3. I have this exact problem in an Excel workbook that I use as a template to generate Word reports.

    My observations are that Excel VBA references to the Excel and Office Object Libraries properly roll back to the correct version of the reference, but a reference to a newer version of Word does not roll back. For example. If I save the workbook in Excel 2013, the references point to Excel, Office, and Word Object Libraries 15.0. When I open this workbook in Excel 2003, Excel and Office Object Libraries have been rolled back to 11.0, but the Word Object Library is still pointing to 15.0. So I only have to change the Word reference, but I cannot expect any of my users to do that. (Going from an older version of Excel to a newer version does not have any issues)

    My work around right now is to save the published template in the oldest version it might be used in (2003 – 11.0) so that all users opening the workbook will have full functionality regardless of the version they open it with. The next step was to add error checking to make sure the workbook would not be opened in an older version than it was last saved in. Basically, I have a BeforeSave event that writes the current version of Excel into a specific cell. Whenever the workbook is opened next, it compares the workbook version with the Excel version and informs the user if functionality has been lost due to the workbook being saved in a newer version of Excel than they are using. A bit frustrating, but it handles the error anyway.

    • Tim, thanks for the info! I might try your solution if all else fails…

      It appears that Excel is becoming less compatible across the board as the days go by. We’re getting plagued by the “Excel found unreadable content” error in many of the workbooks we had been previously using without problems. We’re even seeing the error in workbooks from third party clients.

    • I actually just came across another option which is better (but not best):

      Look at Crystal’s response to this thread:
      https://groups.yahoo.com/neo/groups/MS_Access_Professionals/conversations/topics/80921

      Basically, use early binding (having a reference to Microsoft Word Object Library) for development, then change your declarations (like from DIM myWord as Application.Word to DIM myWord as Object), replace referenced keywords with constants (myDoc.ActiveWindow.View.Type = wdPrintView becomes myDoc.ActiveWindow.View.Type=3), and remove the library reference for deployment. Use the Immediate window to find constant values (ctrl + G, then something like “? wdPrintView” [enter]). You’ll need to have the library referenced while finding constants in the Immediate window.

      Hope this helps. I just successfully implemented this on my workbook. I was able to save in 2013, then open in 2003 with full functionality.

      Tim

    • Late binding is the way to go. It’s what I’m using now as well – you will find it mentioned near the end of this article.

  4. Hi,
    When trying to uncheck the MISSING: library from the Tools\Reference dialog, I would get the message “Can’t perform requested operation”. I found that if you open the excel file with macros disabled, then open Visual Basic from the Developer tab, you can remove the missing library without getting the error. I understand that this is not a perfect programmatic solution but it does help get the file saved by the Office 2010+ user to work.
    Regards,
    Paula

  5. The best way to awoid such situation is to use late binding to objects instead of adding reference. When you use late binding the host(eg.Excel) will search for corresponding com object in his object list, and attach to project the proper one.

    dim something as object
    set something = CreateObject("Word.Application")

  6. hey everyone,
    does anyone know if it is possible to use late binding for PowerPoint.ShapeRange object type? I recently upgrade from MS office 2010 to 2013 and seems it is not possible to reference the PowerPoint libraries directly from excel vba anymore (if I understood it correclty)

    where I get the specific error is here:

    Dim shp As PowerPoint.ShapeRange
    Set shp = PowerPointApp.ActivePresentation.Slides(MySlideArray(x)).Shapes.PasteSpecial(DataType:=2)
    With PowerPointApp.ActivePresentation.PageSetup
    shp.LockAspectRatio = False

    End With

    it would be great if someone could give me a hand on that…I’m kind of lost with several vbas I inherited from a colleague of mine…

    Thanks in advance!

  7. Just to add to the solution Paula already posted, you can actually use her method to open your file and get the macros to work again. Not programatically elegant, but good in a pinch:

    Open Excel (the program, not your broken file), and disable all macros in the trust centre options
    Open your file, but DON’T allow macros when it prompts you
    Go to VBA, tools, references, and un-check the references at the top marked as “MISSING:”
    Finally, scroll down the list and enable the newer versions of the missing references (so if you disabled “MISSING: Microsoft Office 14.0 Object Library”, scroll down and enable “Microsoft Office 15.0 Object Library” instead)
    Save your file, and bingo – it should work fine :).

Leave a Reply

Your email address will not be published. Required fields are marked *