TM1 formulas to values in VBA with PAFE API

Ideas and tips for enhancing your TM1 application
Post Reply
Wim Gielis
MVP
Posts: 2713
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:

TM1 formulas to values in VBA with PAFE API

Post by Wim Gielis » Fri Apr 09, 2021 3:46 pm

Hello all,

I am quite sure I posted about this topic some time ago but I cannot find back the topic.

Here is working code in VBA PAFE. Of course, the usefulness is somewhat less compared to zapping TM1 formulas in Perspectives since the PAFE ribbon has buttons to click on. But there are still advantages in my code :D

Code: Select all

Enum ScopeToInspect
    All_Workbooks = 0
    Active_Workbook = 1
    Active_Sheet = 2
    Current_Selection = 3
End Enum

Sub TM1_DB_to_values_PAFE()

    ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    ' PURPOSE:
    ' change cells with TM1 formulas to values
    ' ONLY TO BE USED IN PAFE, NOT PERSPECTIVES
    ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

    Dim wb                    As Workbook
    Dim ws                    As Worksheet
    Dim m1                    As Boolean
    Dim m2                    As XlCalculation
    Dim iScopeToInspect       As ScopeToInspect
    Dim o                     As Object

    '===================================================
    iScopeToInspect = -1
    On Error Resume Next
    iScopeToInspect = InputBox("Where do you want to search for TM1 formulas:" & vbNewLine & vbNewLine & _
                               "(0) all sheets in all open workbooks" & vbNewLine & _
                               "(1) all sheets in the active workbook" & vbNewLine & _
                               "(2) the active worksheet" & vbNewLine & _
                               "(3) the current selection", "Scope", 2)
    If iScopeToInspect = -1 Then Exit Sub

    '===================================================

    Set o = Application.COMAddIns("CognosOffice12.Connect").object.AutomationServer.Application("COR", "1.1")

    m1 = Application.ScreenUpdating
    If m1 Then Application.ScreenUpdating = False

    m2 = Application.Calculation
    If m2 <> xlCalculationManual Then Application.Calculation = xlCalculationManual

    Select Case iScopeToInspect
    Case All_Workbooks

        'loop through all workbooks in the application
        For Each wb In Application.Workbooks

            wb.Activate
            o.UnlinkBook

        Next

    Case Active_Workbook: o.UnlinkBook

    Case Active_Sheet: o.UnlinkSheet

    Case Current_Selection: o.UnlinkSelection

    End Select

    If Application.ScreenUpdating <> m1 Then Application.ScreenUpdating = m1
    If Application.Calculation <> m2 Then Application.Calculation = m2

End Sub
For some of the choices, PAFE will throw a message box for confirmation.
If anyone knows a way to skip this msgbox, please let me know.

Have a good weekend !
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: Avoid circular references https://github.com/wimgielis

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

Re: TM1 formulas to values in VBA with PAFE API

Post by Paul Segal » Mon Apr 12, 2021 3:45 pm

Hi Wim,

It's version-related - from 11.0.56 I don't think it will give you the confirmation messages.
Paul

Wim Gielis
MVP
Posts: 2713
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: TM1 formulas to values in VBA with PAFE API

Post by Wim Gielis » Mon Apr 12, 2021 5:46 pm

Paul Segal wrote:
Mon Apr 12, 2021 3:45 pm
Hi Wim,

It's version-related - from 11.0.56 I don't think it will give you the confirmation messages.
Thanks Paul. I have PAFE version 61 on my own laptop and it still gives the confirmation msgbox'es.
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: Avoid circular references https://github.com/wimgielis

Post Reply