Word macro referencing Excel worked in Office 2007 but fails in 2010

Question

I created a VBA macro in Word 2007 that determines if Excel and a specific Excel workbook is open, opens the workbook (and Excel if needed), then performs various tasks in the workbook.

Under Tools/References, I checked Microsoft Word 12.0 Object Library, Microsoft Office 12.0 Object Library, and Microsoft Excel 12.0 Object Library. The macros ran just fine. I then recently moved to a new PC and Office 2010 and neither of the commands below produces results - appExcel continues to be Nothing.

Set appExcel = GetObject(, "Excel.Application")

Set appExcel = CreateObject("Excel.Application")

I have searched high and low for specific info on how to remedy this, but to no avail. I've found various info that points to either both the 2007 and 2010 dlls being registered, or the one that is necessary not being registered, but nowhere can I find instructions on how to register or unregister either. Nor can I find clearly the file names involved. I'm hoping someone in Stack Exchange can help!

Answer 1

Dim excelapp As Object
Set excelapp = CreateObject("Excel.Application")

Is what you're looking for. If you don't see it then use excelapp.visible = true.

Answer 2

While the fix recommended by Raystafarian helped, the full solution included removing the following registry key:

HKEY_LOCAL_MACHINE\SOFTWARE\Classes\TypeLib{00020813-0000-0000-C000-000000000046}\1.8

I'm now a very happy camper.

Answer 3

Fix the API references as follows:

1. In Excel press Alt+F11 to go to the coding window
2. Open the Tools menu and choose References
3. Uncheck the Missing: item in the list
4. Scroll down and add a reference to the new version of Microsoft Word.