TM1 tools excel add-in not working in Excel 64 bit
-
- 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
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
-
- Screen shot of VB error message
- Capture.PNG (17.61 KiB) Viewed 15960 times
- 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 excel add-in not working in Excel 64 bit
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.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.
"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.
-----------
Before posting, please check the documentation, the FAQ, the Search function and FOR THE LOVE OF GLUB the Request Guidelines.
- gtonkin
- MVP
- Posts: 1192
- Joined: Thu May 06, 2010 3:03 pm
- OLAP Product: TM1
- Version: Latest and greatest
- Excel Version: Office 365 64-bit
- Location: JHB, South Africa
- Contact:
Re: TM1 tools excel add-in not working in Excel 64 bit
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:
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:
Hope this helps.
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
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
-
- 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
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?
-
- 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
I am also interested in knowing where to actually put that code...
Thanks
Thanks
- Steve Rowe
- Site Admin
- Posts: 2410
- Joined: Wed May 14, 2008 4:25 pm
- OLAP Product: TM1
- Version: TM1 v6,v7,v8,v9,v10,v11+PAW
- Excel Version: Nearly all of them
Re: TM1 tools excel add-in not working in Excel 64 bit
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.
Note, totally untested.
Technical Director
www.infocat.co.uk
www.infocat.co.uk
Re: TM1 tools excel add-in not working in Excel 64 bit
Hi,
Just wondering if anyone got this working?
Thanks,
Tom
Just wondering if anyone got this working?
Thanks,
Tom
-
- 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
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.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 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 excel add-in not working in Excel 64 bit
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.kbrcpa@gmail.com wrote: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.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.
"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.
-----------
Before posting, please check the documentation, the FAQ, the Search function and FOR THE LOVE OF GLUB the Request Guidelines.
- 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
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.
-
- 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
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,
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,
- 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 excel add-in not working in Excel 64 bit
Fancy meeting you here.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,
![Very Happy :D](./images/smilies/icon_e_biggrin.gif)
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.
-----------
Before posting, please check the documentation, the FAQ, the Search function and FOR THE LOVE OF GLUB the Request Guidelines.
- 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
Alan,
Following up. Any chance that you may have taken a look at updating TM1Tools to 64-bit?
George
Following up. Any chance that you may have taken a look at updating TM1Tools to 64-bit?
George
- 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 excel add-in not working in Excel 64 bit
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.
-----------
Before posting, please check the documentation, the FAQ, the Search function and FOR THE LOVE OF GLUB the Request Guidelines.
-
- Posts: 48
- Joined: Thu Nov 19, 2009 10:38 pm
- OLAP Product: Tm1
- Version: 10.2.2
- Excel Version: 2010
Re: TM1 tools excel add-in not working in Excel 64 bit
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.
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 699 times
J.Rizk
Tm1 for everyone
Tm1 for everyone
Re: TM1 tools excel add-in not working in Excel 64 bit
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![Mr. Green :mrgreen:](./images/smilies/icon_mrgreen.gif)
![Wink ;)](./images/smilies/icon_e_wink.gif)
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
![Mr. Green :mrgreen:](./images/smilies/icon_mrgreen.gif)
Yeon