VBA Worksheet change not taking into account new TM1 entry

VBA Worksheet change not taking into account new TM1 entry

Postby Catherine » Fri May 25, 2012 9:36 am

Hello,

I have an Excel slice, in which I want to intercept entries to launch a TI process. Each time a user entries a new data in a specific range of cells (containing DBRW formulas), I want a TI process to run to update some related data in an other cube.

I use the VBA event Worksheet_Change, which takes into account the new entry. But the data that has been changed is not in the cube until the sub ends. That means that my TI process runs with the old value. :(
It seems that the link between the cell and the cube (ie DBRW formula) is done after the end of the actions asked in the sub Workksheet_change.

I hope my question is clear. Don't hesitate to ask if you need some more explanation.
Have you ever met this problem? Do you have some ideas?

Thanks in advance
Catherine
Catherine
 
Posts: 96
Joined: Wed May 20, 2009 7:30 am
Location: Rennes, France
OLAP Product: TM1
Version: 9.4 MR1 FP3 - 9.5.2 - 10.1
Excel Version: 2007

Re: VBA Worksheet change not taking into account new TM1 ent

Postby declanr » Fri May 25, 2012 9:42 am

If you have a look around the forum there are plenty of other posts relating to the order in which DBRWs are executed, more so around the reason that you can't use 1 DBRW in the reference of another but I believe the same principles apply to what you are experiencing.
declanr
MVP
 
Posts: 955
Joined: Mon Dec 05, 2011 11:51 am
Location: Edinburgh, United Kingdom
OLAP Product: Cognos TM1
Version: 9.5 10.1 and 10.2 mainly
Excel Version: 2010 and 2013

Re: VBA Worksheet change not taking into account new TM1 ent

Postby Catherine » Fri May 25, 2012 10:13 am

declanr wrote:If you have a look around the forum there are plenty of other posts relating to the order in which DBRWs are executed, more so around the reason that you can't use 1 DBRW in the reference of another but I believe the same principles apply to what you are experiencing.


Thanks for your answers.
I know some problematics about order of DBRWs. But I didn't find anything related to VBA, and more especially to worksheet change event.
Does anybody have an idea how to solve/to workaround my problem?
Catherine
 
Posts: 96
Joined: Wed May 20, 2009 7:30 am
Location: Rennes, France
OLAP Product: TM1
Version: 9.4 MR1 FP3 - 9.5.2 - 10.1
Excel Version: 2007

Re: VBA Worksheet change not taking into account new TM1 ent

Postby declanr » Fri May 25, 2012 10:25 am

Can't you just stick an action button in for the user to initiate after changing values?

I understand that the ability to have the process run automatically via VB would be a benefit but I tend to avoid using VB with TM1 where possible simply due to the fact that I have experienced too many times the hassles of upgrading tm1 just to find that there is some special VB code that no longer works or equally vb code that no longer works after upgrading excel.

Its all down to personal preference of course but I try to fall more on the side of "future-proofing" models where possible as opposed to making them look flashy in the intermediate time-frame.
declanr
MVP
 
Posts: 955
Joined: Mon Dec 05, 2011 11:51 am
Location: Edinburgh, United Kingdom
OLAP Product: Cognos TM1
Version: 9.5 10.1 and 10.2 mainly
Excel Version: 2010 and 2013

Re: VBA Worksheet change not taking into account new TM1 ent

Postby asutcliffe » Fri May 25, 2012 10:50 am

I tend to side with Declan and avoid VBA as much as possible. It's just one more thing that can break.

What happens if you explicitly call TM1Recalc before triggering the TI process? If the only data that should change is the cell pointed to the DBRW, I don't see that this should matter but may be worth a try.

It also sounds like you want to have data elsewhere change dynamically in response to the change the user is making. I assume you've considered using a rule to achieve this rather than TI in the first place or am I missing the point?
asutcliffe
Regular Participant
 
Posts: 162
Joined: Tue May 04, 2010 10:49 am
OLAP Product: Cognos TM1
Version: 9.4.1 - 10.1
Excel Version: 2003 and 2007

Re: VBA Worksheet change not taking into account new TM1 ent

Postby lotsaram » Fri May 25, 2012 2:25 pm

I think the order of when TM1 takes over the Worksheet_Change event versus when you can also try and capture the event just is what it is. (I have been very frustrated in the past by the fact that the FollowHyperlink event is actually the AfterFollowHyperlink event and there is no BeforeFollowHyperlink, but hey you learn to live with it.)

In this case though do you REALLY need to automate to that extent as opposed to trusting the users to click a button to process further updates? Or why can't you have a process at the conclusion that runs after all such updates that might be triggered when the user exits the sheet or even just entirely in the background?
lotsaram
MVP
 
Posts: 2307
Joined: Fri Mar 13, 2009 11:14 am
Location: Switzerland
OLAP Product: TM1, CX
Version: 10.1 10.2
Excel Version: 2010 2013 365

Re: VBA Worksheet change not taking into account new TM1 ent

Postby Catherine » Mon Jun 04, 2012 8:12 am

asutcliffe wrote:What happens if you explicitly call TM1Recalc before triggering the TI process? If the only data that should change is the cell pointed to the DBRW, I don't see that this should matter but may be worth a try.

Thanks for the idea of explicitly calling TM1Recalc but it does not work.

asutcliffe wrote:It also sounds like you want to have data elsewhere change dynamically in response to the change the user is making. I assume you've considered using a rule to achieve this rather than TI in the first place or am I missing the point?

You're not missing the point. I have considered using a rule but views are too long to display, considering the rule complexity, and our big volume of data.

Thanks all for your suggestions. I will use an action button, and run the process after all entries in the sheet.
Catherine
 
Posts: 96
Joined: Wed May 20, 2009 7:30 am
Location: Rennes, France
OLAP Product: TM1
Version: 9.4 MR1 FP3 - 9.5.2 - 10.1
Excel Version: 2007


Return to Cognos TM1

Who is online

Users browsing this forum: Bing [Bot] and 6 guests

Loading