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!