TM1 Forum

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