Excel/VBA Automation Errors due to Office Service

Question

After installing all latest windows updates my Excel VBA code is showing an automation error at the very start of the first module. It contains some forms as well as numerous modules of VBA code. However, when removing the forms (and associated code) everything is fine.

The same thing happened about a year ago after some other windows updates. Back then deleting the .exd files did the trick, but the current updates (including Service Pack 3) seem to be different.

What is going on and how can I get the forms to work? All the code is unchanged and has survived all previous windows/office updates. Running Windows 7 (same happening on Vista machine) and Office 2007.

Answer 1

Same issue here today after performing the update for Office 2007 Excel. Oddly, one system had no issues despite the update - the newest system. So after some inspection, I noticed this trouble-free system had different version of MSCOMCTL.OCX in windows\sysWOW64 then others. Here are the details of this activeX Size: 1,070,152 bytes created: 6/3/2012 modified: 6/6/2012 product version: 6.01.9834 So I copied this to the trouble one and it did the trick.

Also, you might want to check if these application extensions are present in windows\sysWOW64 or windows\system32 for 32-bit system FM20.DLL (Modified Date: 7/7/2011, version 12.0.6604.1000, 1.13MB) FM20ENU.DLL (Modified Date: 10/24/2008, version 12.0.6413.1000, 32.3KB) Not sure where you can download these. I cannibalized them from other systems.

Answer 2

I used to see this a lot with Visual Basic and Crystal Reports; either due to a bad deployment or DLL Hell.

So, my guess is that it's not the code but perhaps some 3rd party controls (OCXs, DLLs) you're using.

Check your References (In VBA IDE, click Tools and References) and to see where each DLL is pointing.

See if any of the dates on the libraries are different than a working machine and you may find the culprit.

---

Edit

The OP found the problem by following the steps above.

The fix for this particular issue is to unregister mscomctl.ocx:

regsvr32 /u mscomctl.ocx 

Then register it:

regsvr32 mscomctl.ocx

Answer 3

Un- and registering the MSCOMCTL.ocx did the trick for me:

1. Open an administrative-level command prompt. Click START, and type RUN in the run command line. On Windows Vista and Windows 7 you will see CMD.EXE appear in the quick search list. Right click CMD.EXE and left click Run As Administrator. A black command prompt opens.

2. If on a 64-bit machine, type the following: regsvr32.exe /u C:\Windows\SysWOW64\MSCOMCTL.OCX

Hit ENTER then type regsvr32.exe C:\Windows\SysWOW64\MSCOMCTL.OCX If on a 32-bit machine, type the following: regsvr32.exe /u C:\Windows\System32\MSCOMCTL.OCX hit ENTER then type
regsvr32.exe C:\Windows\System32\MSCOMCTL.OCX Your Office program should work again after doing this.

Please see also: http://blogs.technet.com/b/the_microsoft_excel_support_team_blog/archive/2012/08/15/quot-unspecified-automation-error-quot-after-applying-ms12-060.aspx

Answer 4

son of a gun...I re-registered C:\Windows\System32\MSCOMCTL.OCX and everything works again. Thanks so much to all that pointed that out.

Answer 5

Had the same issue on all of our terminal servers after one of the updates last night. Registering mscomctl.ocx fixed the problem for all users.