TM1 Tools not working in Excel 2016 (32 bit)

Post Reply
kbrcpa@gmail.com
Posts: 4
Joined: Wed Apr 22, 2015 4:27 pm
OLAP Product: TM1
Version: 10.2.2
Excel Version: 2013

TM1 Tools not working in Excel 2016 (32 bit)

Post by kbrcpa@gmail.com »

We have been utilizing TM1 Tools developed by Alan Kirk and team (TM1 tools v1.0 build 158) successfully in Excel 2013 (32 bit) for several years. Recently we updated to Excel 2016 (32 bit - 16.0.4266.1001) and we are getting no options when clicking on the down arrow for TM1 Tools (historically got all of the proper choices).
2016-07-08_1-28-01.jpg
2016-07-08_1-28-01.jpg (25.21 KiB) Viewed 5337 times
We utilize TM1 Tools all the time so one can easily imagine the high level of frustration. Any suggestions/assistance would be greatly appreciated.
User avatar
Alan Kirk
Site Admin
Posts: 6606
Joined: Sun May 11, 2008 2:30 am
OLAP Product: TM1
Version: PA2.0.9.18 Classic NO PAW!
Excel Version: 2013 and Office 365
Location: Sydney, Australia
Contact:

Re: TM1 Tools not working in Excel 2016 (32 bit)

Post by Alan Kirk »

kbrcpa@gmail.com wrote:We have been utilizing TM1 Tools developed by Alan Kirk and team (TM1 tools v1.0 build 158) successfully in Excel 2013 (32 bit) for several years. Recently we updated to Excel 2016 (32 bit - 16.0.4266.1001) and we are getting no options when clicking on the down arrow for TM1 Tools (historically got all of the proper choices).
2016-07-08_1-28-01.jpg
We utilize TM1 Tools all the time so one can easily imagine the high level of frustration. Any suggestions/assistance would be greatly appreciated.
That's interesting; first, are you sure it's 32 bit? The 32 bit version of Excel 2016 that I have running on 64 bit Windows 10 is 16.0.6741.2021, though this is part of an Office 365 subscription. The drop-down appears normally for me, as do the buttons (which I note are missing on yours as well).

Is the location of the add-in trusted in the Trust Center? Is there a chance that there is some other well-meaning but stupid security policy that disables macros in place? The absence of both the buttons and the menu (both of which are dynamically created by code when the add-in loads) suggests that it's something along those lines.
"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.
ASzewczyk
Posts: 2
Joined: Tue Jan 17, 2017 10:42 am
OLAP Product: TM1
Version: 10.2.2
Excel Version: 365

Re: TM1 Tools not working in Excel 2016 (32 bit)

Post by ASzewczyk »

I have the same issues: no problems with Excel 2010. Now, Excel 2016 356 gives the same result (picture attached).
Our IT support checked carefully security options and they cannot find solution…

Indeed, it is very handy tool. Help would be appreciated a lot.
Attachments
Excel_version.png
Excel_version.png (1.77 KiB) Viewed 5120 times
Excel_365.jpg
Excel_365.jpg (3.62 KiB) Viewed 5120 times
User avatar
Alan Kirk
Site Admin
Posts: 6606
Joined: Sun May 11, 2008 2:30 am
OLAP Product: TM1
Version: PA2.0.9.18 Classic NO PAW!
Excel Version: 2013 and Office 365
Location: Sydney, Australia
Contact:

Re: TM1 Tools not working in Excel 2016 (32 bit)

Post by Alan Kirk »

ASzewczyk wrote:I have the same issues: no problems with Excel 2010. Now, Excel 2016 356 gives the same result (picture attached).
Our IT support checked carefully security options and they cannot find solution…

Indeed, it is very handy tool. Help would be appreciated a lot.
Similar to the questions that I asked the previous poster, but more specifically:
(a) What's the exact path that you have the add-in on?
(b) is it a local path, or a network path? (Or, for that matter, a cloud path like OneDrive)?
(c) Do you have that exact path set as a Trusted Location?

Here's the reason that I'm asking that; it's not something that I found with TM1Tools but with another macro-enabled workbook.

I had my OneDrive folder (in my case, F:\Users\UsereName\OneDrive) set as a Trusted Location in Office 2010, with "Subfolders allowed". Because as incredible as it is for those walking genetic defect notices in Redmond to believe, I do not actually put potentially virally infected files into my own folder. And I don't give anyone else access to that folder. So yeah, if there is a folder under that path I kinda trust myself not to have put a freaking virus into it.

In Office 2010 this worked without a hitch.

Then along came the putrid, stinking piece of floating excrement that passes itself off as a spreadsheet program called Excel 2016.

Excel 2016 (along with the rest of the Office 2016 family) was formulated on the assumption that everyone works on the Microsoft campus at Redmond, or works remotely at the local Starbucks just down the road from there which has a 10 terabyte per nanosecond WiFi connection. (Bear with me, you'll see where I'm going with this.) For example, the Help files are now only available on line and when people asked for offline availibility to be returned, the Office development team's response was to stick their middle finger up in the users' collective face. If you are (as I often am) commuting in an area which is a wireless dead zone, the only "Help" that you will get from Office Help is advice to check your Internet connection, and then try and guess the solution.

Another example; the "Recent Folders" list has been removed from the File -> Open command. You only get recent files. The reasoning behind that is that everyone should be saving to OneDrive anyway, notwithstanding that even those of us who do use OneDrive do not save to the root folder and therefore the absence of the recent folders list still makes it take far longer than it should to find the files that we want, especially for ones which were from a few weeks prior. (The list was recently returned to the File => Save As command (for Office 365 users only, not 2016 standalone packages) after a large user outcry. Microsoft patted itself on the back for being sooooo responsive to user needs, with not a hint of apology or contrition for having screwed us in the first place.)

So, what's the significance of all this?

The old model of OneDrive was to save to the OneDrive folder and the OneDrive application would then sync the files to the sever.

The new Office 2016 model is to write the frapping files directly to the OneDrive server; see the aforementioned "everybody lives in a fantasy world of 24/7 100% reliable internet connections". That means that the OneDrive folder is kinda-sorta more or less like a network location, even if it's actually just a folder on your hard disk.

I discovered the upshot of this when I opened an .xslm in Excel 2016 from a subfolder of OneDrive and none of the VBA code worked. I checked that OneDrive was still a trusted location. Yup. I checked that subfolders were still trusted. Yup. So I spewed a stream of invective in both English and Italian at my monitor in the hope that the vibes would somehow make their way through the network to the Microsoft campus and cause the roof to collapse in the Office development team's building, entombing all of them inside and instantaneously improving the overall quality potential of global software by maybe 20%.

Alas it didn't happen.

So I sat and pondered for a few minutes about the New! Paradigm! Shift!, and noticed that "Allow trusted locations on my network" was not checked. It didn't need to be for OneDrive to work the way I told it to in Office 2010. But sure enough, I found that if you don't have that box checked, Microsoft, who knows far better than you do what is good for you, will still treat OneDrive as an untrusted location regardless of your explicit instructions to the contrary.

Now, you may not be storing it on OneDrive at all. But with this setting you can see how it may be that a location appears to be trusted, but is actually not.

As I said to the previous poster, there is nothing intrinsically wrong with the add-in in Excel 2016; I use it every time I can bring myself to face that worthless piece of junk. (I still use 2010 for most things.) And it does work. That's why I'm betting on a trust issue being at the root of this, even if it's something cryptic like the example that I described above.
"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.
ASzewczyk
Posts: 2
Joined: Tue Jan 17, 2017 10:42 am
OLAP Product: TM1
Version: 10.2.2
Excel Version: 365

Re: TM1 Tools not working in Excel 2016 (32 bit)

Post by ASzewczyk »

So I sat and pondered for a few minutes about the New! Paradigm! Shift!, and noticed that "Allow trusted locations on my network" was not checked. It didn't need to be for OneDrive to work the way I told it to in Office 2010. But sure enough, I found that if you don't have that box checked, Microsoft, who knows far better than you do what is good for you, will still treat OneDrive as an untrusted location regardless of your explicit instructions to the contrary.

Now, you may not be storing it on OneDrive at all. But with this setting you can see how it may be that a location appears to be trusted, but is actually not.
Thank you very much ! Our IT colleagues followed your suggestion and the tool works now.
Ultraview
Posts: 1
Joined: Mon Oct 22, 2018 5:43 am
OLAP Product: TM1
Version: 10.2.1
Excel Version: 2010 and 2016

Re: TM1 Tools not working in Excel 2016 (32 bit)

Post by Ultraview »

Hi Alan,

I am having the same issue, then I fixed by ticking "Trust Network".

But I've got another problem when clicked on the magnifying glass, I only got half window.
[img]
Capture.PNG
Capture.PNG (7.46 KiB) Viewed 3965 times
[img]

Is there a way to fix this?

Thank you
User avatar
Alan Kirk
Site Admin
Posts: 6606
Joined: Sun May 11, 2008 2:30 am
OLAP Product: TM1
Version: PA2.0.9.18 Classic NO PAW!
Excel Version: 2013 and Office 365
Location: Sydney, Australia
Contact:

Re: TM1 Tools not working in Excel 2016 (32 bit)

Post by Alan Kirk »

Ultraview wrote: Sun Jan 06, 2019 10:57 pm Hi Alan,

I am having the same issue, then I fixed by ticking "Trust Network".

But I've got another problem when clicked on the magnifying glass, I only got half window.
[img]Capture.PNG[img]

Is there a way to fix this?

Thank you
That's the one bit of the thing that was written by Martin Ryan rather than me, but I doubt it's down to the code; I'd lay odds on it being a video driver issue. I'm not surew I can help with that but I'd need a bit more info like all details of the Excel version and a dump from the dxdiag (Windows Key + R, dxdiag ) diagnostic tool) to see if anything jumps out at me.
"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.
Post Reply