Automation Error when loading TM1p.xla

[RajKerai]

Hello,
Been a while but I'm back and could do with some assistance.


I am getting an "Automation Error" message when loading the TM1p.xla addin which works fine on all but one Desktop machine.

The install approach adopted has been to copy the TM1 bin folder to the C:\Program Files\Applix\Bin and place a short cut against the TM1p.xla
(need to get users access so have not gone through the usual of running the install as desktop policies do not allow for running .exe files)

When the user loads the addin, what looks like a VBA message appears with the prompt "Automation Error".
After this the add-in fails to load.

Can anyone shed some light on this.

Version of Perspectives 9.0 SP3 (9.0.3.196)
Excel 2003 on XP Operating System

[TJMurphy]

Have had this on one machine regularly and on another machine once.

The only thing that made it go away was a full "Detect and Repair" exercise on Office 2007. And rumour has it that it's back on that machine again too (I've not been able to check yet). As that PC is a different model to others which don't have the problem I'm not even convinced it isn't some sort of nasty little incompatibility in that particular type of PC.

I went Googling on it at the time and found not a lot of information but some hints that it was a nasty little sod that required restarting PCs and was very intermittent.

Tony

[RajKerai]

Resolution :
1. Close the addin and all Excel sessions
2. Go to the following path in Windows Explorer: C:\Documents and Settings\<<Your user ID>>\Application settings\Microsoft\Excel\
3. There should be a *.xlb file there (in Office 2003 called 'Excel11.xlb') which holds the command bar customisation details. Delete this file
4. Restart Excel and restart the addin. (The *.xlb file will be automatically re-created but this appears to clear the error).

Basically the xlb holds information about the custom toolbars. I am assuming that in my case these were somehow getting corrupt.

[Paul Segal]

I've just had this error happen on a 2008 R2 server running Terminal Services (and yes it was painful). So just to add my tuppence to this thread, my fix was to uninstall and then reinstall the TM1 client - no automation error after a reboot. Deleting the .xlb files seemed to have no effect.

[paulsimon]

Raj/Paul

The Automation error might relate to the In-Spreadsheet Browser. To cure it you can drag the 3 files below onto regsvr32.exe in the windows\system32 directory.

tm1xl.ocx
tm1prc.dll
tm1dasrv.dll

You should get a message saying Registration succeeded if all goes well.

If this still doesn't cure it, you might try looking for .EXD files as these also relate to Windows Forms.

Regards

Paul Simon

[dumbomichael]

hi Guys

we have this error too. it started on one of our pc's yesterday, and has now spread to four in the office. i have tried all of the above suggestions, as well as deleting and reinstalling tm1, but still the error is present.

the only one i havent tried is

The Automation error might relate to the In-Spreadsheet Browser. To cure it you can drag the 3 files below onto regsvr32.exe in the windows\system32 directory.

tm1xl.ocx
tm1prc.dll
tm1dasrv.dll

You should get a message saying Registration succeeded if all goes well.

as i cannot find the 3 filesunder that directory. has anyone else been able to come up with any other solution?

[dumbomichael]

turns out it was an excel update from microsoft that caused the issue. the it department downloaded a fix and have corrected everyone's computer

[Fionnuala]

hi,
I'm getting this error now- does anyone have a link to the MS fix ?
Thanks,
Fionnuala

[ignacex]

Hi, IBM posted a fix for this on their site, simply un-registering and re-registering mscomctl.ocx works (at least it did for us) : http://www-01.ibm.com/support/docview.w ... wg21608271

[Fionnuala]

Thanks, that worked for me too

[jameswebber]

I found in CE 10.1 (Xcelerator) that although I had the Microsoft Visual C++ 2005 Redistributable installed I needed the SP1 version.

http://www.microsoft.com/en-us/download ... px?id=5638

I didn't need to re register anything

[TJMurphy]

We just had this one this morning, spent half the day working out the fix and scripting it so that users could do it automatically.

For us, mostly Office 2007 and Windows XP, we had to re-register the three tm1 files, *and* unregister / reregister the mscomctl.ocx files.

This appears to have sorted it out - I'm still waiting for some of our Windows 7 users to try it out though.

Tony

[lotsaram]

We usually run about 2 months behind in pushing out the MS updates. Of course the update when it comes is unannounced. I just can't wait for the fun and games when we get this one. Still may as well be prepared and pre-do the scripts to unreg and rereg the components. Tony - any chance you could share what you have?

[TJMurphy]

Hi Lotsa,
This script seems to be working well for our Windows XP users. For Windows 7 it's turned into a nightmare partly because it needs elevated privileges but mostly because I don't have a Windows 7 PC to test it on! This script is slightly cut down to remove anything incriminating but hopefully it should still work for you. No laughing at my poor VBScript code efforts please!

Code: Select all

' VB Script Code
' Fix for the August Microsoft Office patch that
' seems to have de-registered or corrupted the registry
' entries for various TM1 and Office controls
' Created 17.08.2012, Tony Murphy

option explicit

' Two common folders for TM1
' For us people either installed using the full CD or more commonly a cut down installer that I created
' Assuming they installed in the default folders there should only be two places for the TM1 files (any more and this won't work)
const tm1Folder1 = "C:\Program Files\Cognos\TM1\bin\"
const tm1Folder2 = "C:\Program Files\GI TM1\bin\"
' At one point I had extra lines in for a Windows7 version that didn't work
const WindowsXP = "c:\windows\system32\mscomctl.ocx"

 
dim fso		' File System Object
dim tm1Dir
dim wincontrol
dim tm1Files
dim sCmd

set fso = createobject("Scripting.FileSystemObject")

' Check which folder the files are installed to - limited number of options unfortunately
' There is nothing in the IBM notes about having to re-register the TM1 Controls but on our PCs we found we had to do these on Win XP machines.
' On Windows 7 PCs we only had to do the mscomctl.ocx file.

if fso.fileexists (tm1folder1 & "tm1xl.ocx") then
	tm1dir = tm1folder1
	msg "Run Fix in : " & tm1folder1
end if

if fso.fileexists (tm1folder2 & "tm1xl.ocx") then
	tm1dir = tm1folder2
	msg "Run Fix in : " & tm1folder2
end if

if fso.fileexists (windowsxp) then
	wincontrol = windowsxp
end if

if tm1dir = "" or wincontrol = "" then
	msg "TM1 Files not found. Please contact ???"
else
	RunFixes
end if


sub RunFixes
dim sCMD
dim wShell
dim bReturnValue 

	set wshell = wscript.createobject ("WScript.Shell")
	
	scmd = "regsvr32 /s """ & tm1dir & "tm1xl.ocx"""
	breturnvalue =	wshell.run (scmd,1, true)
	if breturnvalue <> 0 then
		msg "Error Registering tm1xl.ocx. Please tell ???"
	end if
	
	scmd = "regsvr32 /s """ & tm1dir & "tm1prc.dll"""
	breturnvalue = wshell.run (scmd,1, true)
	if breturnvalue <> 0 then
		msg "Error Registering tm1prc.dll. Please tell ???"
	end if

	scmd = "regsvr32 /s """ & tm1dir & "tm1dasrv.dll"""
	breturnvalue = wshell.run (scmd,1, true)
	if breturnvalue <> 0 then
		msg "Error Registering tm1dasrv.dll. Please tell ???"
	end if
	
	
	scmd = "regsvr32 /u /s " & wincontrol
	breturnvalue = wshell.run (scmd,1, true)
	if breturnvalue <> 0 then
		msg "Error unregistering mscomctl.ocx. Please tell ???"
	end if
	
	scmd = "regsvr32 /s " & wincontrol
	breturnvalue = wshell.run (scmd,1, true)
	if breturnvalue <> 0 then
		msg "Error Registering mscomctl.ocx. Please tell ???"
	end if
	
	msgbox "Finished"
	
end sub


sub Msg (sText)
	msgbox stext
end sub

For Windows 7, we eventually got the users to do some typing!
The instructions on IBM's website are correct but for us missed a bit (probably obvious to others with more W7 experience). We told people to hit Start, type in cmd and then right-click on cmd.exe and Run as Administrator before typing in the two lines. Just running a cmd window and typing in the two lines did not work (unregistering worked but re-registering didn't and it wasn't obvious from the error message that it was a privilege issue).

Hope the above is useful for you.

[lotsaram]

Thanks Tony! I'm sure this will be useful not just for my team but many others also.

[dumbomichael]

this is what we did on our pc's. might be an easier solution.


Click Start, Run

Type the following in:
regsvr32 /u C:\Windows\System32\mscomctl.ocx
click ok to run it

then click start, run
type the following in:
regsvr32 C:\Windows\System32\mscomctl.ocx
click ok to run it

When you open Excel, this error should now be gone and TM1 will work again.

[PlanningDev]

Anyone havi gnthis problem and not had this fix it? Originally it worked for me but then I installed 10.1 and FP1 and I still get the error.

Just noticed the 3 tm1 files. I'll try that too. I'm using Windows 7 and Office 2010

[cambista]

The post and link from ignacex worked like a charm for me. I am working out of a MS Windows Server 2003 R2 Ent x64/TM1 9.1 SP3/MS Excel 2007.

"Report this postReply with quoteRe: Automation Error when loading TM1p.xla
by ignacex » Thu Aug 16, 2012 1:21 pm

Hi, IBM posted a fix for this on their site, simply un-registering and re-registering mscomctl.ocx works (at least it did for us) : http://www-01.ibm.com/support/docview.w ... wg21608271"

[whitej_d]

There are some cases where un-registering and re-registering the file won't work (to do with old versions of the file). In this case you need to get a working version from another machine and copy it over into the broken client machine's C:\Windows\System32 (for 32 bit) or C:\Windows\syswow64 (for 64 bit) and re-register it as described above.