Undo after save in Excel 2016 with TM1 add in

Post Reply
PhilNeal
Posts: 2
Joined: Thu Sep 21, 2017 7:18 am
OLAP Product: TM1
Version: IBM Cognos TM1 64bit
Excel Version: Office 2016

Undo after save in Excel 2016 with TM1 add in

Post by PhilNeal » Thu Sep 21, 2017 12:50 pm

Recently forced to upgrade to Office 2016 (Excel 2016).
With TM1 add-in (perspectives) activated, every time I save any workbook the next action I take undoes the prior action. Example: enter "1000" into a cell, save workbook, type "2000" into another cell, hit enter, lose both the 1000 and the 2000!!
It is 100% consistent happening until I deactivate the TM1 add-in.
Operating on Windows 8, but same issue for team members using Windows 10.
Have checked with our TM1 administrator that I am using most current version of the TM1 excel add-in (perspectives).
Surprisingly good old google didn't show anyone else with same issue.... Could it be native to our network or servers somehow?
Any help or ideas appreciated - otherwise revert to Office 2013 somehow....? Manual calculate on.

User avatar
gtonkin
MVP
Posts: 602
Joined: Thu May 06, 2010 3:03 pm
OLAP Product: TM1
Version: PAL 2.0.1
Excel Version: 2016 64-bit
Location: JHB, South Africa
Contact:

Re: Undo after save in Excel 2016 with TM1 add in

Post by gtonkin » Thu Sep 21, 2017 5:00 pm

I have a colleague who narrowly escaped being committed for saying the same thing. He is running Office 365 (2016 64-bit) and Planning Analytics as the TM1 server, TM1 adding for PAL-think he is on 2.0.2 but will need to confirm.
What versions of TM1 and Client are you running?
If you create a basic macro and F8 into it so as to lock other events from triggering, do you have the same behaviour?
Same behavior with TM1 started as Addin vs double-clicking TM1p.xla?

User avatar
Alan Kirk
Site Admin
Posts: 5805
Joined: Sun May 11, 2008 2:30 am
OLAP Product: TM1
Version: 9.5.2 64 bit moving to 10.2.2
Excel Version: 2010
Location: Sydney, Australia
Contact:

Re: Undo after save in Excel 2016 with TM1 add in

Post by Alan Kirk » Thu Sep 21, 2017 7:20 pm

No, you're right, there's definitely a problem here. I was going to raise a post asking whether anyone else had experienced it as well but I wanted to see if I could isolate exactly when it occurs. It sounds like you may have, though I was encountering this without (intentionally) saving.

I've successfully used TM1 10.2.2 with Office 2016 in the past without experiencing this, and it's something that I've only noticed recently; as in, when I was doing something last weekend.

However there is one key question; are you using Excel 2016 retail or Excel 2016 out of Office 365?

The difference is that the Office 365 version is continually having new features added. There is one feature that was added recently which is autosave.

"Yay", thought I, "about 25 years overdue! I'll finally be able to set it to save a file to disk every n minutes!"

But no, the Microsoft Office Development team, being the inept, useless, customer-hating pack of pillocks that it is, naturally took a good idea and turned it into a freaking abomination. First, it only works when you save to a cloud location, and second while you can turn it off for specific workbooks you can't disable it completely. And it's not saving every n minutes it is doing it continuously, which would not only do wonders for your bandwidth consumption but also makes me wonder whether it is syncing the copy in the cloud with the copy in memory, and if so what impact that is having on the way Perspectives takes over the Worksheet Change event.

That said though; I'm encountering this even if I turn off Autosave in that particular workbook. However what I haven't yet had time to test is whether it still happens (a) if I save to a workbook which is stored on a local disk and (b) whether I can reproduce this in a copy of Excel 2016 which has not been updated. (My notebook's case cracked apart a few weeks ago and while it still works it's been powered down since then. If I keep it offline when I fire it up it should still have the "old" Excel 2016 / Office 365 on it and I can check whether this is happening here. Which I'm pretty sure it wasn't, because it's not something that escapes attention.)

One other point; when I was going through the Request For Enhancements a few weeks ago I noticed that there was one asking for TM1 to support Excel in Office 365 which seemed odd as... it did. Or it did back then. So I'm thinking that this is something to ship off to IBM support but I have no idea whether they'll be able to work around it if, as I suspect, this is down to the fricking morons at Redmond and their brain-dead bright ideas.

For the record, I'm running Office 365 32 bit, Excel version 1707, build 8326.2107 on the Current Channel. (Yeah, you'd think you'd be able to copy that but no, you have to re-type it. **** you, Microsoft.) The TM1 version is 10.2.2 FP7. And if I unload the TM1 add-in... it stops happening.
"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.

User avatar
gtonkin
MVP
Posts: 602
Joined: Thu May 06, 2010 3:03 pm
OLAP Product: TM1
Version: PAL 2.0.1
Excel Version: 2016 64-bit
Location: JHB, South Africa
Contact:

Re: Undo after save in Excel 2016 with TM1 add in

Post by gtonkin » Thu Sep 21, 2017 7:35 pm

@Alan, any difference in behaviour when loading as an addin vs opening TM1P.xla?
My laptop is on PAL 2.0.1 with Office 365, 64-bit -has the new auto save-will run some tests in the morning and reply if I find any similarities.
BTW, the issue my colleague is having does not seem to be limited to saving happens whilst capturing-previously entered values seem to undo "themselves"

User avatar
Alan Kirk
Site Admin
Posts: 5805
Joined: Sun May 11, 2008 2:30 am
OLAP Product: TM1
Version: 9.5.2 64 bit moving to 10.2.2
Excel Version: 2010
Location: Sydney, Australia
Contact:

Re: Undo after save in Excel 2016 with TM1 add in

Post by Alan Kirk » Thu Sep 21, 2017 7:41 pm

Alan Kirk wrote:
Thu Sep 21, 2017 7:20 pm
But no, the Microsoft Office Development team, being the inept, useless, customer-hating pack of pillocks that it is, naturally took a good idea and turned it into a freaking abomination.
Hmm, this is an interesting coincidence... right after I posted that I got a pop-up balloon in the system tray;

"Sono disponibili aggiornamenti di Office. Fare clic qui per installare gli aggiornamenti di Office."

"You what??" I hear you say. It's telling me that there are updates available. Probably ones that put "Yeah, well *** you too!" in the status bar as a personal message to me, but let's see what else they install and whether it makes any change to this.

Oh, why is it in Italian? Ah yeah, forgot to tell you about that other piece of amazingly competent programming. The Office 365 install is of course in English, the ribbon is in English, the default language is English. However I installed the Italian language pack since I do maybe 25% of my writing in Italian. (And to be fair, the language packs are pretty good; no, let's be totally fair, they are very good. In fact, they are the only reason that I use Office 2016 outside of making training films, otherwise I'd still be using 2010.) BUT... once you install a language pack, all update dialogs and notifications appear in that language.

I don't really care because I can still read them, but don't ever be tempted to install a language pack for a language that you don't know.

(In answer to George's post which was made while I was typing this; I always load as an add-in, but I haven't tested loading as a file. I'll do so after Office fa gli aggiornamenti. And you're right, I hadn't associated it with saving but I do think that Phil Neal is onto something because even with the autosave turned off I still see "Saving ..." in the workbook's title bar.)
"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.

PhilNeal
Posts: 2
Joined: Thu Sep 21, 2017 7:18 am
OLAP Product: TM1
Version: IBM Cognos TM1 64bit
Excel Version: Office 2016

Re: Undo after save in Excel 2016 with TM1 add in

Post by PhilNeal » Thu Sep 21, 2017 11:18 pm

Hi all
Thank you all for the responses (clearly I'm on the other side of the world - Sydney).
@Alan Kirk - I'm running 10.2.2 on Windows 365 Office 2016 32-bit.
@gtonkin - both Addin and TM1p.xla methods create same impact.
I've switched off Auto-save (in excel options) but it is any save event that creates this effect (and hit Ctrl+S out of habit about 10 times a minute!).
@gtonkin - the F8 idea didn't work either.
I'll work out how to a) lodge the bug on IBM (never done before) and b) work out a reliable way to revert to Office 2013 if MS lets you do that!
Phil.

User avatar
Alan Kirk
Site Admin
Posts: 5805
Joined: Sun May 11, 2008 2:30 am
OLAP Product: TM1
Version: 9.5.2 64 bit moving to 10.2.2
Excel Version: 2010
Location: Sydney, Australia
Contact:

Re: Undo after save in Excel 2016 with TM1 add in

Post by Alan Kirk » Thu Sep 21, 2017 11:20 pm

OK, i stronzi piccoli hanno fatto gli aggiornamenti.

(Hey, if Salvo Montalbano can say it (and often does), so can I. Although I notice that the English sub-titlers at SBS (the network that broadcasts the series here) often, shall we say... "moderate" Salvo's language at times. )

I'm now on version 1708 (Build 8431.2079) and they've changed the name of the channel to "Monthly Channel".

No matter; I'm getting exactly the same results. Even if I turn autosave off, even if I have the file saved to a local disk... as soon as I save, then enter another value, it will undo that value and the one before it if the TM1 add-in is loaded. As per George's post, it makes no difference whether the add-in is loaded through the add-ins list or through the file -> open dialog.

I haven't yet been able to test it on the broken notebook and unfortunately I have a project that I need to get done today before I can.

I would raise this with IBM Support but the only place I have 2016 /10.2.2 FP7 is on a standalone PC whose purpose is solely environment testing. There is nobody at my workplace who has yet been infected with the 2016 virus outside of that one machine. It would probably be better coming from someone who is stuck with this problem operationally.

So... are you on support, Phil? (Edit: I was writing this post as you were posting the one above this, which I obviously hadn't seen at the time.)
"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.

User avatar
Alan Kirk
Site Admin
Posts: 5805
Joined: Sun May 11, 2008 2:30 am
OLAP Product: TM1
Version: 9.5.2 64 bit moving to 10.2.2
Excel Version: 2010
Location: Sydney, Australia
Contact:

Re: Undo after save in Excel 2016 with TM1 add in

Post by Alan Kirk » Fri Sep 22, 2017 12:06 am

Alan Kirk wrote:
Thu Sep 21, 2017 11:20 pm
I haven't yet been able to test it on the broken notebook and unfortunately I have a project that I need to get done today before I can.
Although as it turned out, it took less time than I thought.

I can confirm that it is not happening on Excel 2016 version 1706, build 8229.2086 with the TM1 (10.2.2 FP7) add-in loaded. It looks like the issue started with version 1707 which, coincidentally or not and I think not, is when the Autosave feature came out.
"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.

User avatar
Steve Rowe
Site Admin
Posts: 1783
Joined: Wed May 14, 2008 4:25 pm
OLAP Product: TM1
Version: 10.2.2., PAW
Excel Version: Nearly all of them

Re: Undo after save in Excel 2016 with TM1 add in

Post by Steve Rowe » Fri Sep 22, 2017 10:42 am

Hi, Just wanted to note that we have not been able to reproduce.

Excel 2016 MSO(16.0.8326.2076) Version 1707 which I think is the version being reporting but on an ordinary Office install, so not Office 365.
TM1 Perspective version PA 2.0 Refresh Pack 2 (11.0.00200.998)
Windows Server 2016 Standard

With perspectives loaded and logged into an instance we were able to make simple entries into cells save and do other entries without impact. Worksheet contained a slice.

Wim Gielis
MVP
Posts: 1748
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1
Version: PAL 2.0
Excel Version: 2016
Location: Brussels, Belgium
Contact:

Re: Undo after save in Excel 2016 with TM1 add in

Post by Wim Gielis » Fri Sep 22, 2017 11:28 am

Hi,

Big problem !

Environment:

Office 32 bit
Microsoft Office 365 Business
Version 1707 (build 8326.2107 click-to-run)
PAL (TM1 11.0.200.998)

It's not linked to the new auto save functionality because it's turned off for me now.

1. Create a new workbook (Ctrl-N)
2. Change 1 cell
3. Save the file to the desktop
4. Change another cell
5. End result: 2 cells changed

Load the Perspectives addin.

1. Create a new workbook (Ctrl-N)
2. Change 1 cell
3. Save the file to the desktop
4. Change another cell
5. End result: ALL CELLS ARE EMPTY
Best regards,

Wim Gielis

Excel Most Valuable Professional, 2011-2014
http://www.wimgielis.com ==> 105 TM1 articles and a lot of custom code
Newest blog article: Looping over input files

User avatar
gtonkin
MVP
Posts: 602
Joined: Thu May 06, 2010 3:03 pm
OLAP Product: TM1
Version: PAL 2.0.1
Excel Version: 2016 64-bit
Location: JHB, South Africa
Contact:

Re: Undo after save in Excel 2016 with TM1 add in

Post by gtonkin » Fri Sep 22, 2017 12:01 pm

Same Office build as Wim, can confirm disappearing with either 10.2.2 FP4 Perspectives or PA 2.0.1 Perspectives addin.
If I Step into Macro Code and repeat the steps, whatever event is being triggered is no longer triggered and my values remain.
Will play a bit more later...

User avatar
Alan Kirk
Site Admin
Posts: 5805
Joined: Sun May 11, 2008 2:30 am
OLAP Product: TM1
Version: 9.5.2 64 bit moving to 10.2.2
Excel Version: 2010
Location: Sydney, Australia
Contact:

Re: Undo after save in Excel 2016 with TM1 add in

Post by Alan Kirk » Fri Sep 22, 2017 12:11 pm

Steve Rowe wrote:
Fri Sep 22, 2017 10:42 am
Hi, Just wanted to note that we have not been able to reproduce.

Excel 2016 MSO(16.0.8326.2076) Version 1707 which I think is the version being reporting but on an ordinary Office install, so not Office 365.
Yes, but I did say that that was the difference. Retail Office 2016 is not updated continuously in feature content in the way that Office 365 is. The version number may be the same but it may relate to a completely different incarnation. Look up at the top left; do you see an Autosave toggle? If not, then it's a "different" Excel 2016 version 1707. (I believe that in "1707" the first 2 digits are the year and the last two the iteration. It would not be surprising if two parallel product streams ended up with the same version number.)
Wim Gielis wrote:It's not linked to the new auto save functionality because it's turned off for me now.
I really wouldn't assume that if I were you. I had already said that turning it off doesn't stop it from happening but the autosave feature wasn't introduced in isolation. It acts in conjunction with the new Activity pane which allows you to revert to former versions of the workbook. (That does require autosave to be on but I'll bet that some of the background code runs anyway.) I would imagine that even the Microsoft Office team isn't stupid enough to keep thousands of separate copies of a file in the cloud so it's far more likely that what it's tracking is changes. And as we know, the process that Perspectives goes through to ensure that typing a value into a cell doesn't obliterate a DBRW (the old three step process) is very non-standard. It's quite possible that it is interacting with Microsoft's Latest! Bright! Idea! in a way that the Redmondites had never even bothered to anticipate (and wouldn't have cared about even if they had).
"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.

User avatar
gtonkin
MVP
Posts: 602
Joined: Thu May 06, 2010 3:03 pm
OLAP Product: TM1
Version: PAL 2.0.1
Excel Version: 2016 64-bit
Location: JHB, South Africa
Contact:

Re: Undo after save in Excel 2016 with TM1 add in

Post by gtonkin » Thu Sep 28, 2017 9:38 am

I have loaded a service request for this issue: 69715-689-864 Not sure who will be able to access but it has been logged.
Will post updates as I receive.

User avatar
Alan Kirk
Site Admin
Posts: 5805
Joined: Sun May 11, 2008 2:30 am
OLAP Product: TM1
Version: 9.5.2 64 bit moving to 10.2.2
Excel Version: 2010
Location: Sydney, Australia
Contact:

Re: Undo after save in Excel 2016 with TM1 add in

Post by Alan Kirk » Sat Oct 28, 2017 12:59 am

I've just been updated to version 1709 (build 8528.2139); the problem seems to have gone away (and DBRWs, active forms etc are still working).

Is anyone else still seeing it?
"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.

Wim Gielis
MVP
Posts: 1748
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1
Version: PAL 2.0
Excel Version: 2016
Location: Brussels, Belgium
Contact:

Re: Undo after save in Excel 2016 with TM1 add in

Post by Wim Gielis » Sat Oct 28, 2017 12:22 pm

I cannot reproduce the problem above anymore.

Excel: Version 1709
TM1: PAL 2.0.3
Best regards,

Wim Gielis

Excel Most Valuable Professional, 2011-2014
http://www.wimgielis.com ==> 105 TM1 articles and a lot of custom code
Newest blog article: Looping over input files

User avatar
gtonkin
MVP
Posts: 602
Joined: Thu May 06, 2010 3:03 pm
OLAP Product: TM1
Version: PAL 2.0.1
Excel Version: 2016 64-bit
Location: JHB, South Africa
Contact:

Re: Undo after save in Excel 2016 with TM1 add in

Post by gtonkin » Sat Oct 28, 2017 2:39 pm

Can also concur with the above-just updated to Build 1709 (8528.2147) and the pre-halloween gremlins have gone, for now!

Post Reply