TM1 Tools Issues And Discussions (Was *KEY_ERR Tracer)

[John D]

Off the top of my head I can't think of anything that's (shall we say) "region-sensitive" in the tool itself, but it could be the result of a mismatch between the client settings and the server-side settings, especially given that it works when your regional settings (I presume that you mean on the client side) are in English/US. What's the language of the box that the server is running on?

I'll do some further tests and see what I can find.

Hi Alan.
Yes, it is on the client side, but it doesn't change anything if I do the same on the server.
If you want to reproduce it, you only need to change the regional setting on your clientmachine to Danish, and then try to copy a range of 2 cells with 6,50 into a slice.

If you need more info please say so.

[hbell]

Alan

... have finally had some time to look at debugging the slow speed. The delay comes in this line:

Err.Raise vbObjectError + 1003

Apologies - I don't know how to paste a screenshot here to give you better context, but the surrounding code is as follows: (ignore the Application.statusbar line - I added that for tracking purposes)

'Search in order of likelihood
If s_CellFormula Like "*DB*(*" Then
'DBR, DBRW, DBS, DBSW
If s_CellFormula Like "*DB?(*" Or s_CellFormula Like "*DB?W(*" Then
Application.StatusBar = "Error Raise"
Err.Raise vbObjectError + 1003
'DBRA, DBSA

This line seems to take about a second to execute for each formula. The "Error Raise" statusbar message I inserted above appears to be constantly fixed when the code is executed even though I inserted another message immediately on entering the Error trapping sequence. I infer from this that the rest of the code loop executes faster than the eye can see (or at least the screen refresh).

Does that give you any other clues on where to look for possible culprits in our environment? Might anything be triggered by a run-time error in VBA?

thanks .........hugh

[image2x]

I'm hoping someone with 9.5.2 FP1 might be able to test the numeric paste functionality in an Active Form?

At least for a co-worker and myself, it appears to be broken. Pasted values briefly flash *KEYERR before returning to their prior values.

Thanks,
-- John

[declanr]

I am not entirely sure if it is related but I did find when using active forms (within a book that had many active forms) pressing Shift F9 caused the old data to re-appear whereas pressing F9 submitted the correct values... not entirely sure if this helps your situation or not. Most of my work with Active Forms now leaves users entering values through TM1 Web so the whole situation changes somewhat.

[image2x]

Declanr, was your shift-F9 vs F9 experience with the TM1 Tools paste function?

For kicks, I've tried three different refresh methods but have the some result:

Code: Select all

'Refresh any consolidations. I'm using the Excel command
'rather than the Recalc macros which have caused me a HUGE
'amount of grief over the years.
If CBool(GetSetting(gSC_APP_NAME, _
 gSC_REG_SECT_OPTS, gSC_REGKEY_BULKPASTE_CALC, "True")) Then

    ActiveSheet.Calculate
    'Application.Run "TM1RECALC1"
    'Application.Run "TM1RECALC"

[declanr]

Sorry that was my poor memory coming into play that was just a standard Active Forms use

I have found however if you use the TM1 Tools paste function that if in AutoCal you won't see the error message but if you do it with manual calc on I get an error stating that it is unable to read the clipboard (this is using the TM1 Tools add-in with 9.5.2) so it seems to be finding the same issue that you have.

[Martin Ryan]

I just ran on 9.5.2 FP1 without any problems. It was pretty slow, but it worked.

The VBA is not protected so you could step through and see if you can figure out what line exactly is causing the problem.

What version of Excel are you running?

EDIT: I was trying with a slice, not an active form. I've replicated it with an active form. Will see if I can figure out what's going on.

EDIT 2: I think it is related to Declan R's issue. The write back in active forms seems different from slices as evidenced by shift+F9 not working even if you type the number in yourself. I stepped through the code and managed to replicate the *KEY_ERROR problem, but when I traced the formula there was nothing wrong with it. Will have to see if Alan can figure out more as built this particular TM1 tool and knows more about active forms too.

Martin

[image2x]

Martin -- thank you for checking.

Sounds like we'll need to let Alan weigh-in when he has time.

Edit: I should note that this may or may not be something that was working and then broke in FP1. I've only tried it in FP1.

[Alan Kirk]

Martin -- thank you for checking.

Sounds like we'll need to let Alan weigh-in when he has time.

Edit: I should note that this may or may not be something that was working and then broke in FP1. I've only tried it in FP1.

Yes, I want to run it through the packs. just to be clear, though, you haven't also applied any hot fixes have you? If so, which one(s)? (They were coming out at such a prodigious rate that I haven't been able to maintain the old threads with HF lists; and IBM still refuses to publish the release notes. )

[image2x]

just to be clear, though, you haven't also applied any hot fixes have you?

No, ours should be a "clean" FP1. In fact, from what I could tell, FP1 wouldn't even let you install over a HF'ed installation which meant, for us, an uninstall followed by 9.5.2 install followed by FP1.

[solverxyz]

i have a stupid question.
when I try to load xla into my EXCEL, I get a message:
an error has occured while creating the toobar and menu.

then I have no add-in toolbar and menu.

Does anyone know how it happened?

[Alan Kirk]

i have a stupid question.
when I try to load xla into my EXCEL, I get a message:
an error has occured while creating the toobar and menu.

then I have no add-in toolbar and menu.

Does anyone know how it happened?

This is in relation to TM1 Tools? If so there should have been an error number and description after the line that you're describing. Could you please provide those along with your Windows version, Excel version, service pack, etc.

[harrytm1]

Hi Alan,

I updated some Windows Updates today. After that, the Tm1 Tools failed to load in Excel.

the Excel error (using Exel 2010) is:
Object library invalid or contains references to object definitions that could not be found.

I'm using Tm1 9.5.2 FP2.

[nick_leeson]

Mate, Funny that a couple of blokes and myself had the same problem this morning. Have a look at the below link to resolve the issue.
http://www.lessanvaezi.com/delete-exd-f ... lid-error/

[Alan Kirk]

Hi Alan,

I updated some Windows Updates today. After that, the Tm1 Tools failed to load in Excel.

the Excel error (using Exel 2010) is:
Object library invalid or contains references to object definitions that could not be found.

I'm using Tm1 9.5.2 FP2.

I haven't looked at Nick Leeson's link but I can tell from the title what it'll be. We've had the same thing come up in relation to the TM1 addin itself over and over. Those .exd files are a bloody menace, Microsoft's equivalent to the equally useless TM1 .tbd files. Kill 'em all, and see whether that sorts it out.

[clarencechien]

Hi Alan,

I'm very thankful to you and TM1 tools. It's very helpful to paste data into Excel, and it works well.
However, I notice the TM1 Tools Addin Release post about "formatted numbers"
(http://www.tm1forum.com/viewtopic.php?f=21&t=2515)

- 14-Apr: The Bulk Copier does not seem to care for formatted numbers. This will be fixed in the next release.
In the meantime, ensure that you strip the formatting off before you copy the data.

Does it mean I couldn't copy a percentage and paste into TM1? I've tried that I could copy a decimal and paste it.
But it doesn't work on a percentage number such as "12.45%", both "paste into formulas" and "send to string elements"
do not work.

Do you have any idea about that? Is it the same thing that you mean?

My Env is:
TM1 9.5.2
Office 2010

Many thanks
Clarence Chien

[Steve Vincent]

Sort of related to the add-in but i'm guessing more a general issue with the way Office 2010 behaves.

I need the TM1ElIsAnc function in a host of reporting but can't rely on or desire to have all the users have the add-in loaded. My solution was to copy the required code in to each report, but it seems 2010 behaves differently to 2003 and it's just tripped me up in a big way. As the code is unaltered, when i open the reports i end up having 2 sources of VBA with the same code (file & add-in). What's happening is Excel is remembering the local file path of the add-in when saving the file, rather than what 2003 did which was use the file first. It means no one opening the reports can run them unless the add-in is installed.

My solution should be easy (renaming the code in the reports to avoid the clash) but it wasn't something i had appreciated when we migrated to 2010 last year....

[yyi]

just a quick thought but a different module name might work?

Code: Select all

Debug.Print Module1.ELISANC(vDim, vAnc, vDsc)

[rks1]

Hello Community!

Is it somehow possible to to load TM1-Tools all the times I open the Perspectives Add-in? If possible through the same link?

Thank you

[Wim Gielis]

Hi rks1

That's possible. I wrote a short article about how to do it. You can find it on my personal website,
then navigate to the TM1 articles, then browse to "June 2012: Excel shortcuts and TM1".

Wim