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.
TM1 Perspectives - Prompt when closing Excel?
-
- Posts: 28
- Joined: Wed Aug 08, 2018 10:01 am
- OLAP Product: IBM Cognos TM1
- Version: 10.2.2
- Excel Version: 2016
- 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?
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.
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
Shop at Amazon
Jimbo PC Builds on YouTube
OS: Mac OS 11 PA Version: 2.0.7
-
- 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?
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.
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.
-
- MVP
- Posts: 3113
- 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?
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.
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
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
-
- 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?
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!
-
- MVP
- Posts: 2832
- 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?
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.
-
- 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?
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:
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)!
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
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)!