Excel VBA, PAFX: make a cell static

Post Reply
Wim Gielis
MVP
Posts: 2584
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1, Jedox
Version: PAL 2.0.8
Excel Version: Microsoft 365
Location: Brussels, Belgium
Contact:

Excel VBA, PAFX: make a cell static

Post by Wim Gielis » Tue Nov 03, 2020 1:07 am

Hello all,

I am looking at a way to make 1 cell (or a bunch of cells) static, using Excel VBA. In TM1 Perspectives, this wasn't an issue. Something like this would work:

Code: Select all

Sub test()
   
    With ActiveCell
           .Value = .Value   
    End With

End Sub
But not anymore in PAFX (or whateve it is called this week, as Alan would say).

If the cell contains a simple DBRW, the DBRW is not overwritten, contrary to what happens in Perspectives.
So I figured a few workarounds, including:

Code: Select all

Sub test()
   
    Dim z As Variant
   
    With ActiveCell
        z = .Text
        .ClearContents
        .Value = z
    End With

End Sub
All to no avail. The DBRW is stubborn and survives (even after clearing cells, reculating, refreshing, ...).

I do see the idea of being able to paste data over DBRWs without erasing them and I like that. However we should also be able to do what I’m trying to do here.

Did anyone succeed ? Thanks !
Best regards,

Wim Gielis

Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 117 TM1 articles and a lot of custom code
Newest blog article: Using TI to change elemant names, like Biden to Trump

Paul Segal
Community Contributor
Posts: 277
Joined: Mon May 12, 2008 8:11 am
OLAP Product: TM1
Version: TM1 11 and up
Excel Version: Too many to count

Re: Excel VBA, PAFX: make a cell static

Post by Paul Segal » Tue Nov 03, 2020 7:05 am

This should work:

Code: Select all

Application.COMAddIns("CognosOffice12.Connect").Object.AutomationServer.Application("COR", "1.1").ClearSelection
with alerts turned off. If you're on any version earlier than .56 then the alerts won't turn off. .56 and above and they will.
Paul

Wim Gielis
MVP
Posts: 2584
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1, Jedox
Version: PAL 2.0.8
Excel Version: Microsoft 365
Location: Brussels, Belgium
Contact:

Re: Excel VBA, PAFX: make a cell static

Post by Wim Gielis » Tue Nov 03, 2020 7:35 am

Hi Paul,

Thanks ! Version 56 is pretty new and I don’t have it yet.
I will experiment once I have it.
Best regards,

Wim Gielis

Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 117 TM1 articles and a lot of custom code
Newest blog article: Using TI to change elemant names, like Biden to Trump

20 Ton Squirrel
Posts: 13
Joined: Tue Jul 14, 2020 9:53 pm
OLAP Product: TM1
Version: 11.7.00002.1
Excel Version: Office 365

Re: Excel VBA, PAFX: make a cell static

Post by 20 Ton Squirrel » Tue Nov 17, 2020 7:19 pm

This is a bit pedantic but I rather specialize in Excel VBA and would like to offer a suggestion for your code, Wim.

Whenever you are writing values to the worksheet use the Value2 property.

Code: Select all

Sub test()
   
    With ActiveCell
           .Value2 = .Value2   
    End With

End Sub
The standard Value property "formats" the value, particularly currency which gets rounded. Value2 foregoes the checks on variable typing and just provides the value as-is. As an added bonus, Value2 is slightly faster when reading/writing in cells.

I realize that original code isn't being used but... well, I just can't help myself. ;)

Post Reply