Removing TM1 formulae from a sheet

Post Reply
NPTM1
Posts: 13
Joined: Mon Sep 26, 2016 9:59 am
OLAP Product: TM1
Version: 9.5.1
Excel Version: 2010

Removing TM1 formulae from a sheet

Post by NPTM1 » Fri Jul 13, 2018 5:23 pm

Hi

I've been using a macro that I found online to replace TM1 formulae with values. It searches for the formulae beginning with "=DBRW" and "=-DBRW" and various other combinations, which so far has been working great.

However I've now got DBR within IF formulae, and the true/false conditions will change for each cell (leaving and I can't account for every combination within the macro).

Does anyone have any tips on how I can get around this?

I thought about "tagging" the start of each formula with =N("TM1")+ which would return a 0 value, and then having the DBR formula after it, However this only really works for numbers. So something like:

=N("TM1")+IF(A1=B1,DBRW(XXXXX),"OK")

would return 0OK. Also thought about =IF(N("TM1")=0,"","")& but this will only work for strings and not numbers.

Wim Gielis
MVP
Posts: 1748
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1
Version: PAL 2.0
Excel Version: 2016
Location: Brussels, Belgium
Contact:

Re: Removing TM1 formulae from a sheet

Post by Wim Gielis » Fri Jul 13, 2018 6:10 pm

Usually, you should ONLY use:

=DBRW( ... )
and
=DBR(

and that's it.

No funny things like: * 1,000 at the end, and no - sign in front, no =DBRW( ... ) + DBRW( ... )
These manipulations should ideally be done in TM1.

Does this make life more difficult for your macro ? Maybe yes. But that's about it.
Use PAX to convert to hard values. Or extend your macro to check:

=DBRW(
=DBR(
=DBRA(
=DBSS(

Or, use ranges where you know you will only get TM1 formulas.

If you work in a consistent way, this should not be a problem.
Best regards,

Wim Gielis

Excel Most Valuable Professional, 2011-2014
http://www.wimgielis.com ==> 105 TM1 articles and a lot of custom code
Newest blog article: Looping over input files

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

Re: Removing TM1 formulae from a sheet

Post by Steve Rowe » Fri Jul 13, 2018 8:06 pm

http://www.tm1forum.com/viewtopic.php?t=2515

I'm pretty sure the forums TM1 Tool add in as some code for stripping out TM1 formula when they are nested, its pretty old though and may not play well with PAx

Post Reply