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:
- Opening the file in Excel 2007 is very slow and causes the program to stop responding.
- 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.” 
If only that tedious job actually worked…