TM1 tools excel add-in not working in Excel 64 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 excel add-in not working in Excel 64 bit

Post by kbrcpa@gmail.com » Thu Apr 23, 2015 9:03 am

We have been utilizing a wonderful Excel add-in for the past 4 years developed by Alan Kirk and team (TM1 tools v1.0 build 158) - thank you! The latest issue is that we needed to upgrade Excel to a 64bit version (we are running TM1 10.2.2) and have found that the version of the add-in no longer is functional (error message attached - "Compile error - the code in this project must be updated for us on 64-bit systems. Please review and update Declare statements and then mark them with the PtrSafe attribute.". While I am highly dangerous in excel, I have no idea how to go about fixing this issue and am very much hoping there is either a newer version of the Excel add-in to address or otherwise there is another workaround so we can utilize Excel 64-bit. Any suggestions/assistance would be greatly appreciated.
Attachments
Capture.PNG
Screen shot of VB error message
Capture.PNG (17.61 KiB) Viewed 6186 times

User avatar
Alan Kirk
Site Admin
Posts: 5831
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: TM1 tools excel add-in not working in Excel 64 bit

Post by Alan Kirk » Thu Apr 23, 2015 9:54 am

kbrcpa@gmail.com wrote:We have been utilizing a wonderful Excel add-in for the past 4 years developed by Alan Kirk and team (TM1 tools v1.0 build 158) - thank you! The latest issue is that we needed to upgrade Excel to a 64bit version (we are running TM1 10.2.2) and have found that the version of the add-in no longer is functional (error message attached - "Compile error - the code in this project must be updated for us on 64-bit systems. Please review and update Declare statements and then mark them with the PtrSafe attribute.". While I am highly dangerous in excel, I have no idea how to go about fixing this issue and am very much hoping there is either a newer version of the Excel add-in to address or otherwise there is another workaround so we can utilize Excel 64-bit. Any suggestions/assistance would be greatly appreciated.
Creating an Excel 64 bit version of the add-in is on the To Do list; it's just that operationally 32 bit will need to be pried out of my cold, dead hands (for precisely the reason you've stated, but with regard to other add-ins). Since you can have only one "bit" version of Excel on a machine at any one time I'll be setting up a VM with 64 bit Excel to do the development and testing, but I just haven't had time to do it yet. Can't give you a definite time frame yet but it'll be sooner rather than later since I need the 64 bit version of Excel for some new (believe it or not) episodes of TM1 Bytes that are due to be shot in the next fortnight, if I keep to schedule.
"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: 630
Joined: Thu May 06, 2010 3:03 pm
OLAP Product: TM1
Version: PAL 2.0.3
Excel Version: 2016 64-bit
Location: JHB, South Africa
Contact:

Re: TM1 tools excel add-in not working in Excel 64 bit

Post by gtonkin » Thu Apr 23, 2015 5:55 pm

Hi Alan,

Not sure how much tinkering you have done but I had an old template that we needed to test a specific function and found this code does the trick:

Code: Select all

Option Explicit
#If Win64 Then
Declare PtrSafe Function TM1_API2HAN Lib "tm1.xll" () As Long
#Else
Declare Function TM1_API2HAN Lib "tm1.xll" () As Long
#End If
Some posts use VBA7 instead of Win64 but found Win64 to be correct.

Created a workbook for users to more easily check if they had 32-bit or 64-bit Excel and used code similar to above on open. I had two named ranges for data collection purposes:

Code: Select all

Private Sub Workbook_Open()

Dim arrUser As Variant
Dim strUser As String

Application.EnableEvents = False
#If Win64 Then
        Sheet1.Range("INFO.Version").Value = "64-bit"
#Else
        Sheet1.Range("INFO.Version").Value = "32-bit"
#End If

arrUser = Split(Application.UserLibraryPath, "\")
strUser = UCase(arrUser(2))
strUser = Application.UserName

Sheet1.Range("INFO.User").Value = strUser
Application.EnableEvents = True

End Sub
Hope this helps.

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

Re: TM1 tools excel add-in not working in Excel 64 bit

Post by kbrcpa@gmail.com » Tue Apr 28, 2015 9:28 pm

I'd love to be able to utilize one of the additional VBA edits that you are suggesting but am unfamiliar on how to incorporate into the existing. How/where do you properly add to TM1Tools.xla?

tobtm1
Posts: 19
Joined: Tue Feb 17, 2015 1:01 pm
OLAP Product: TM1
Version: 10.1
Excel Version: 2010

Re: TM1 tools excel add-in not working in Excel 64 bit

Post by tobtm1 » Mon Jun 01, 2015 7:36 am

I am also interested in knowing where to actually put that code...

Thanks

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

Re: TM1 tools excel add-in not working in Excel 64 bit

Post by Steve Rowe » Mon Jun 01, 2015 9:58 am

Probably after the option explicit statement at the top of the module. You'll need to comment out the original declare statement.

Note, totally untested.

TomBr
Posts: 30
Joined: Tue Jun 03, 2008 6:56 pm

Re: TM1 tools excel add-in not working in Excel 64 bit

Post by TomBr » Thu Jun 18, 2015 11:58 am

Hi,

Just wondering if anyone got this working?

Thanks,

Tom

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

Re: TM1 tools excel add-in not working in Excel 64 bit

Post by kbrcpa@gmail.com » Fri Jul 08, 2016 5:37 am

Alan Kirk wrote:Creating an Excel 64 bit version of the add-in is on the To Do list; it's just that operationally 32 bit will need to be pried out of my cold, dead hands (for precisely the reason you've stated, but with regard to other add-ins). Since you can have only one "bit" version of Excel on a machine at any one time I'll be setting up a VM with 64 bit Excel to do the development and testing, but I just haven't had time to do it yet. Can't give you a definite time frame yet but it'll be sooner rather than later since I need the 64 bit version of Excel for some new (believe it or not) episodes of TM1 Bytes that are due to be shot in the next fortnight, if I keep to schedule.
Alan - any chance that you have made any progress? We utilize TM1 Tools on a daily basis at it would be wonderful to have it working in a 64 bit environment.

User avatar
Alan Kirk
Site Admin
Posts: 5831
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: TM1 tools excel add-in not working in Excel 64 bit

Post by Alan Kirk » Tue Jul 12, 2016 11:41 pm

kbrcpa@gmail.com wrote:
Alan Kirk wrote:Creating an Excel 64 bit version of the add-in is on the To Do list; it's just that operationally 32 bit will need to be pried out of my cold, dead hands (for precisely the reason you've stated, but with regard to other add-ins). Since you can have only one "bit" version of Excel on a machine at any one time I'll be setting up a VM with 64 bit Excel to do the development and testing, but I just haven't had time to do it yet. Can't give you a definite time frame yet but it'll be sooner rather than later since I need the 64 bit version of Excel for some new (believe it or not) episodes of TM1 Bytes that are due to be shot in the next fortnight, if I keep to schedule.
Alan - any chance that you have made any progress? We utilize TM1 Tools on a daily basis at it would be wonderful to have it working in a 64 bit environment.
Sorry, it's not technically difficult, I just have to get off my lazy backside and set up a 64 bit environment where I can do it. I haven't been inclined to give up 32 bit Excel myself yet so it needs to be a VM since you can't have both installed at the same time. I haven't forgotten about 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.

User avatar
MJS George
Posts: 2
Joined: Fri Apr 28, 2017 7:52 pm
OLAP Product: TM1
Version: 10.2.2
Excel Version: Excel 2010-2016
Location: United States

Re: TM1 tools excel add-in not working in Excel 64 bit

Post by MJS George » Mon May 01, 2017 12:52 pm

Alan, First. Absolutely love tm1tools.xla. Second. Just wondering if you ever got around to porting this tool to 64-bit. We use it quite extensively and I have several users that need to upgrade to Excel 64-bit but are choosing not to because the tool does not work in Excel 64-bit. I know of no another alternative and would really like to continue to use the tool.

l_kothamasu
Posts: 10
Joined: Thu Oct 07, 2010 1:55 am
OLAP Product: IBM Cognos TM1
Version: 10.2.2 Fix pax 4
Excel Version: 2013

Re: TM1 tools excel add-in not working in Excel 64 bit

Post by l_kothamasu » Thu Jun 08, 2017 5:25 am

Hi Alan,

We are also waiting for latest TM1 tools application which supports to Excel 64 bit version.

If by any chance, is there any progress update on this?

Regards,

User avatar
Alan Kirk
Site Admin
Posts: 5831
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: TM1 tools excel add-in not working in Excel 64 bit

Post by Alan Kirk » Thu Jun 08, 2017 5:40 am

l_kothamasu wrote:Hi Alan,

We are also waiting for latest TM1 tools application which supports to Excel 64 bit version.

If by any chance, is there any progress update on this?

Regards,
Fancy meeting you here. :D

Hold that thought until next week. By sheer coincidence that's part of our upgrade process and I have time allocated to it tomorrow. There will be a couple of enhancements to the 32 bit version as well but nothing earth shattering.
"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
MJS George
Posts: 2
Joined: Fri Apr 28, 2017 7:52 pm
OLAP Product: TM1
Version: 10.2.2
Excel Version: Excel 2010-2016
Location: United States

Re: TM1 tools excel add-in not working in Excel 64 bit

Post by MJS George » Thu Jul 27, 2017 5:49 pm

Alan,

Following up. Any chance that you may have taken a look at updating TM1Tools to 64-bit?

George

User avatar
Alan Kirk
Site Admin
Posts: 5831
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: TM1 tools excel add-in not working in Excel 64 bit

Post by Alan Kirk » Thu Jul 27, 2017 6:55 pm

Started on it, haven't finished it yet. I'm overseas for the next few days so I won't be working on it again until next week. Given the increasing interest in 64 bit I plan to do an interim "1.5" release which has SOME of the V2 features plus a 64 bit version just to get the latter out there, but I still need to add a couple of features that my own users need before I swing to a tested 64 bit version.
"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.

jrizk
Posts: 42
Joined: Thu Nov 19, 2009 10:38 pm
OLAP Product: Tm1
Version: 9.4
Excel Version: 2007

Re: TM1 tools excel add-in not working in Excel 64 bit

Post by jrizk » Thu Aug 17, 2017 6:35 am

Hi,

I’ve uploaded TM1Tools.xlam which should work on both 32bit and 64bit Excel.

To note:
* converted xla to xlam
* replaced the toolbar (and disabled the associated code) with the ribbon (Tm1 Tools)
* used Application.FileDialog in place of GetOpenFolder function for the folder selection (for testing – couldn’t seem to get the GetOpenFolder function to work on both 32 and 64bit)

Also, not taking away from anything Alan or anyone else is doing with Tm1 Tools, I’m sure that the next offering will be better than what I’ve uploaded here. This is more of an exercise in providing some ideas on reworking the code for use on 32bit and 64bit Excel.

It’s a bit of a quick and dirty change, and I haven’t spent much time on it - I’ve only just setup a 64bit environment for testing. In saying that some of the declarations may not be correct or the conversions for 64bit may not be required.

Hope it helps.
Attachments
TM1Tools.zip
(493.24 KiB) Downloaded 138 times
J.Rizk
Tm1 for everyone

User avatar
yyi
Community Contributor
Posts: 115
Joined: Thu Aug 28, 2008 4:42 am
Location: Sydney, Australia

Re: TM1 tools excel add-in not working in Excel 64 bit

Post by yyi » Tue Jul 31, 2018 2:01 am

It's good! ;)
thanks for sharing.


Nice to have it on ribbon. Works well with PAX for dynamic reports (after the addin check is disabled)
Maybe the next offering could be some extended functions using "Cognos Office Automation Server object" that installs with PAX :mrgreen:
win2012 svr: tm1 v10.2.2 x64, client v10.2.2 x64/PA 2.0, win10, excel 365.

Post Reply