Dimension Open, Excel 365 (2016) 64 bit and 10.2.2 Automation Error Crash

Post bug reports and the status of reported bugs
Post Reply
User avatar
Alan Kirk
Site Admin
Posts: 6191
Joined: Sun May 11, 2008 2:30 am
OLAP Product: TM1
Version: PA2 Classic (PAW-free zone)
Excel Version: 2010 and 2016
Location: Sydney, Australia

Dimension Open, Excel 365 (2016) 64 bit and 10.2.2 Automation Error Crash

Post by Alan Kirk » Fri Aug 03, 2018 10:47 pm

I haven't submitted this at this point because I need to check whether this is just a "my machine" thing or something that applies to everyone.

Environment: Windows 10 professional 64 bit.
TM1 10.2.2 FP 7
Excel 2016 (64 bit) from Office 365. Note particularly the "64 bit" part which is where I suspect this issue is coming from.

If I select "Dimension Open" in Perspectives to update an .xdi, then:
(a) If the data directory specified is empty, then an empty list will be shown as expected; but
(b) If the data directory has even a single file (.xdi) in it, Excel will generate an "Automation Error" warning, then crash.

If the data directory is empty I can create a new dimension and save it correctly. (Although Excel will shove the ever-accursed "Book1" in my face after it creates the new .xdi and I have to manually change to the new .xdi file.) However once the file is saved, I can't re-open it through Dimension Open because... Automation Error, crash.

(Workaround: It can be opened as a regular file, updated and saved as a dimension without any major problem. other than the irritating Extension Hardening warning which of course I immediately turned off.)

I'm not encountering this with PA2 (the bits that actually work, that is; Perspectives and Web but not PAX because, well, Docker), but in the PA2 test environment (on a different machine) I'm using Excel 2016 (Office 365) 32 bit.

It could therefore be specific to my machine or, what I think is more likely, there's an issue with the VBA code that reads the list of files in the data directory when it's run by 64 bit Excel.

Is anyone else experiencing this in a similar environment?
"To them, equipment failure is terrifying. To me, it’s 'Tuesday.' "
Before posting, please check the documentation, the FAQ, the Search function and FOR THE LOVE OF GLUB the Request Guidelines.

Posts: 1
Joined: Thu Jun 22, 2017 2:17 am
OLAP Product: Tm1
Version: 10.2.2
Excel Version: 2016

Re: Dimension Open, Excel 365 (2016) 64 bit and 10.2.2 Automation Error Crash

Post by penguinsareawesome » Wed Nov 14, 2018 1:32 am

I am getting the same error using PA 2.0.4 with a 64-bit version of Excel 2013. Dimension worksheets are fine when opening using the 32-bit version (on multiple machines) so seems that the issue is the 64bit version of excel/Tm1 add-in.

I am also able to save the dimension on the 64-bit version as long as I open it via the standard excel function, not the Tm1 add-in drop -down option

Posts: 5
Joined: Wed Dec 12, 2018 5:12 am
OLAP Product: TM1
Version: 10.2
Excel Version: 2013

Re: Dimension Open, Excel 365 (2016) 64 bit and 10.2.2 Automation Error Crash

Post by Eighty3 » Mon May 20, 2019 3:47 am

We upgraded from TM1 10.2.1 to PA 2.0 this week and encountered this issue with Microsoft Excel 2013 64 bit

As pointed out by penguinsareawesome, 32 bit works fine

Thanks for the workaround Alan

Is IBM aware of this bug?

Posts: 25
Joined: Thu Sep 07, 2017 8:31 am
OLAP Product: TM1
Version: 10.2.2 / 9.5.1
Excel Version: 2003 2010

Re: Dimension Open, Excel 365 (2016) 64 bit and 10.2.2 Automation Error Crash

Post by jwafro » Tue Jan 07, 2020 1:59 pm

I have a client with the same issue - opening developer options/ "dimension open" causes excel to crash with Excel O365 and Excel 2019 MSO 64 bit.
This same issue has been reproduced using PA 2.0.8 and 10.2.2 under the O365, using Planning Sample.

They need to use the developer feature as they wholesale change the dimensions used in their analysis, and alternative solutions are not as elegant - eg Dimensional editor is clunky, no desire to use TI scripts and CSV inputs either.

We have found that if the local server data directory, once an XDI or XRU file is saved in there will cause Excel to completely crash without error messages when attempting to open a file from that folder. When run directly on the server we got a VBA Automation Error code 440 before the system close.

Creating a new XDI/XRU file in a blank folder works fine, (eg "Dimension New") and loads into the system.

They are maintaining the system using the work around mentioned by others above, eg. Opening the XDI file in excel directly as an excel document and then using the developer/ Dimension Save option.

Anyone heard of a fix from IBM on this?

Paul Coggan
Posts: 15
Joined: Wed Sep 17, 2008 8:44 am

Re: Dimension Open, Excel 365 (2016) 64 bit and 10.2.2 Automation Error Crash

Post by Paul Coggan » Mon Sep 28, 2020 4:38 am

I replicated this issue with PA 2.0.6 and with the current version of 64 bit Office 365 and could not find any reference to any IBM fixes so raised this with them. They have responded to indicate that Excel 2016 and 2019 are supported for Perspectives but Office 365 will not be tested with Perspectives and is thus not supported. The pop up message received when opening a dimension worksheet no longer gives an option to continue with the files open so it appears this is a roadblock unless the registry setting recommended by Alan above can be applied.

In practice we haven't had any issues with the 32 bit version of Office 365. This issue only occurs with 64 bit Office 365.

Post Reply