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
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
If anyone knows a way to skip this msgbox, please let me know.
Have a good weekend !