TM1 Perspectives - Prompt when closing Excel?

Post Reply
jamesbennett
Posts: 28
Joined: Wed Aug 08, 2018 10:01 am
OLAP Product: IBM Cognos TM1
Version: 10.2.2
Excel Version: 2016

TM1 Perspectives - Prompt when closing Excel?

Post by jamesbennett »

I run TM1 through Perspectives. This means that if I mistakenly close all open Excel workbooks, all my TM1 windows close as well. Is there an easy way to force Perspectives to warn me when I try and close Excel with Perspectives still running?

I'm using Perspectives 10.2.2 with Excel 2013.
User avatar
jim wood
Site Admin
Posts: 3951
Joined: Wed May 14, 2008 1:51 pm
OLAP Product: TM1
Version: PA 2.0.7
Excel Version: Office 365
Location: 37 East 18th Street New York
Contact:

Re: TM1 Perspectives - Prompt when closing Excel?

Post by jim wood »

If I remember rightly, if you changed a view it should prompt you to see if you want to change the view? Beyond that I'm not sure if you can as it is an Excel add-in after all,

Jim.
Struggling through the quagmire of life to reach the other side of who knows where.
Shop at Amazon
Jimbo PC Builds on YouTube
OS: Mac OS 11 PA Version: 2.0.7
bgregs
Posts: 77
Joined: Wed Sep 12, 2018 11:19 am
OLAP Product: TM1 / Planning Analytics
Version: 2.0
Excel Version: 2016

Re: TM1 Perspectives - Prompt when closing Excel?

Post by bgregs »

A few ideas come to mind, but nothing pretty. Excel will prompt you to save an open workbook if you have made a change to it, which means we could potentially use that in our favor through one of two options:

A) Put a space or some random character into a cell of the blank workbook that opens on startup
B) Create your own add-in that loads on startup that automatically puts a random character and removes it from a new workbook

Both of these will change the "state" of your Excel workbook from "new" to "updated", which will force a prompt.
Wim Gielis
MVP
Posts: 3105
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1, Jedox
Version: PAL 2.0.9.18
Excel Version: Microsoft 365
Location: Brussels, Belgium
Contact:

Re: TM1 Perspectives - Prompt when closing Excel?

Post by Wim Gielis »

Natively and out of the box: no.
You would need to add VBA code to intercept events, like Workbook_Close, Workbook_Deactivate.
Actually Application level events. Knowing that TM1 hijacks a number of events too, this will not be easy at all.
Best regards,

Wim Gielis

IBM Champion 2024
Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 121 TM1 articles and a lot of custom code
Newest blog article: Deleting elements quickly
jamesbennett
Posts: 28
Joined: Wed Aug 08, 2018 10:01 am
OLAP Product: IBM Cognos TM1
Version: 10.2.2
Excel Version: 2016

Re: TM1 Perspectives - Prompt when closing Excel?

Post by jamesbennett »

Thanks all for the ideas. It's useful to know there is no out-of-the-box solution. I think the VBA route might be best - will give that a try!
tomok
MVP
Posts: 2831
Joined: Tue Feb 16, 2010 2:39 pm
OLAP Product: TM1, Palo
Version: Beginning of time thru 10.2
Excel Version: 2003-2007-2010-2013
Location: Atlanta, GA
Contact:

Re: TM1 Perspectives - Prompt when closing Excel?

Post by tomok »

Closing all open Excel workbooks does not cause open TM1 boxes to close. I just verified that. Closing Excel itself does cause all open TM1 objects to close. What do you expect? Perspectives is an Excel ad-in. No Excel then no Perspectives. Also, if you have changed a view, or subset, then you will automatically get warned when you exit Excel. If you have not changed the view then why do you care? It'll still be there in the "saved" state the next time you open Perspectives. I don't know what you are trying to accomplish.
Tom O'Kelley - Manager Finance Systems
American Tower
http://www.onlinecourtreservations.com/
bgregs
Posts: 77
Joined: Wed Sep 12, 2018 11:19 am
OLAP Product: TM1 / Planning Analytics
Version: 2.0
Excel Version: 2016

Re: TM1 Perspectives - Prompt when closing Excel?

Post by bgregs »

Hi Everyone,

Maybe I'm misunderstanding, but this is extremely easy to do with a custom add-in (however I agree that I don't quite understand why this would be needed). Create a new module in a blank workbook and put the following code in there:

Code: Select all

Private Sub Auto_Open()
    Dim wb As Workbook
    
    Set wb = Workbooks.Add()
    Worksheets("Sheet1").Range("A1").Value = 1
    Worksheets("Sheet1").Range("A1").Value = ""
End Sub
Then save it as an Excel-Addin format and modify your File > Options > Add-ins to load your new add-in on startup (you will need to browse for it). From here on out, Excel will ALWAYS prompt you before you are allowed to close it completely.

Again, not sure why this would be super useful, but I can confirm that this simple macro does the trick (it will load on default, so opening Perspectives directly will still trigger this event)! :D
Post Reply