TM1 Forum

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.

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.

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.

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?

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

Thanks

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.

Hi,

Just wondering if anyone got this working?

Thanks,

Tom

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.

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.

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.

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,

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.

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.

Alan,

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

George

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.

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.

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