The following code adds a button in your Excel toolbars to replace all TM1 formulas in the current book with their values in order to get your worksheets TM1-free.

It has the following features:

• toolbar/button for ease of use
• applies to all sheets in the workbook
• changes all TM1 DB functions (DBRW, DBR, DBA...) and SUBNM/VIEW into values

This code might get included in a future version of the TM1 Open Source Toolkit

/!\ Mind that dynamic spreadsheets will regenerate all the DBRW formulas in the section boundary on a reload unless you remove all the stored names.

A quick equivalent hack is to simply drop the "paste values" icon next to the copy icon in the standard Excel toolbar. So when you need to remove formulas, click top left corner of the sheet and copy + paste values buttons. It does the job just as fast.

-----MODULE1--------------
Function bCommandBarExists(sCmdBarName As String) As Boolean
'test if a given menu exists

    Dim bCbExists As Boolean
        
    Dim cb As CommandBar

    bCbExists = False
    For Each cb In Application.CommandBars
        If cb.name = sCmdBarName Then
            bCbExists = True
            Exit For
        End If
    Next

    bCommandBarExists = bCbExists

End Function

Sub addMenu()
'add "freeze values" entry in TM1 menu

  Dim cmdbar As CommandBar
  Dim toolsMenu As CommandBarControl
  Dim myMenu As CommandBarPopup
  Dim subMenu As CommandBarControl
  
' Point to the Worksheet Menu Bar
  Set cmdbar = Application.CommandBars("Worksheet Menu Bar")

' Point to the Tools menu on the menu bar
  Set toolsMenu = cmdbar.Controls("TM1")
  
' Create the sub Menu(s)
  Set subMenu = toolsMenu.Controls.Add
  

    With subMenu
      .Caption = "Freeze values"
      .BeginGroup = True
      .OnAction = "'" & ThisWorkbook.name & "'!DeleteTM1Formulas" ' Assign Macro to Menu Item
    End With


End Sub
Sub BuildCustomToolbar()
'build a new TM1 toolbar for "freeze values"

Dim oCmdBar As CommandBar
On Error Resume Next

'point to custom toolbar
Set oCmdBar = CommandBars("TM1 Freeze")

'if it doesn't exist create it
If Err <> 0 Then
Set oCmdBar = CommandBars.Add("TM1 Freeze")
Err = 0
With oCmdBar
'now add a control
With .Controls.Add(msoControlButton)
.Caption = "Freeze Values"
.OnAction = "!DeleteTM1Formulas"
.Tag = .Caption
'set the button icon
.FaceId = 107
End With
End With
End If

'make it visible
oCmdBar.Visible = True
'on top
Application.CommandBars("TM1 Freeze").Position = msoBarTop
End Sub

Sub DeleteTM1Formulas()
'replace TM1 formulas with their current values

Dim ws As Worksheet, AWS As String, ConfirmReplace As Boolean
Dim i As Integer, OK As Boolean
    If ActiveWorkbook Is Nothing Then Exit Sub
    i = MsgBox("Replace all TM1 formulas with their current values?", _
    vbQuestion + vbYesNo)
    ConfirmReplace = False
    If i = vbNo Then Exit Sub
    ConfirmReplace = False
    AWS = ActiveSheet.name
    Application.ScreenUpdating = False
    For Each ws In ActiveWorkbook.Worksheets
        OK = DeleteLinksInWS(ConfirmReplace, ws)
        If Not OK Then Exit For
    Next ws
    Set ws = Nothing
    Sheets(AWS).Select
    Application.ScreenUpdating = True
End Sub

Private Function DeleteLinksInWS(ConfirmReplace As Boolean, _
    ws As Worksheet) As Boolean
'replace formulas with their values

Dim cl As Range, cFormula As String, i As Integer
    DeleteLinksInWS = True
    If ws Is Nothing Then Exit Function
    Application.StatusBar = "Deleting external formula references in " & _
        ws.name & "..."
    ws.Activate
    For Each cl In ws.UsedRange
        cFormula = cl.Formula
        If Len(cFormula) > 0 Then
            If Left$(cFormula, 5) = "=SUBN" Or Left$(cFormula, 3) = "=DB" Or Left$(cFormula, 5) = "=VIEW" Then
                    If Not ConfirmReplace Then
                        cl.Formula = cl.Value
                    Else
                        Application.ScreenUpdating = True
                        cl.Select
                        i = MsgBox("Replace the formula with the value?", _
                            vbQuestion + vbYesNoCancel, _
                            "Replace external formula reference in " & _
                             cl.Address(False, False, xlA1) & _
                             " with the cell value?")
                        Application.ScreenUpdating = False
                        If i = vbCancel Then
                            DeleteLinksInWS = False
                            Exit Function
                        End If
                        If i = vbYes Then
                            On Error Resume Next
                            ' in case the worksheet is protected
                            cl.Formula = cl.Value
                            On Error GoTo 0
                        End If
                    End If
            End If
        End If
    Next cl
    Set cl = Nothing
    Application.StatusBar = False
End Function

Attached below is the TM1 Open Source Toolkit developed by James Wakefield

v 1.0.1 adds:

• vba code for running TI processes/chores
• vba for running MDX reports

v 1.0 includes:

• Treeview control on form showing TM1 Servers with cubes and public views. An Imagelist control is used to populate the treeview with images.
• Button on Treeview form allowing user to select view and be written to Excel
• Button on Treeview form allowing user to select view and be shown equivalent MDX statement
• Button on Treeview form allowing user to select view and be shown equivalent XML statement
• form in vba that people can easily use to collect text commentary into TM1
• KillTM1 module of Robert Gardiner's that kills DBRW'S

The following code will:

• load TM1 add-in
• hide the TM1 toolbars (most people do not need spreading, developer.. tools)
• log you on your TM1 server
• open the Server Explorer
• expand the applications and cubes

so you are just 1 click away from accessing your TM1 data :)

Replace the "\\path\to\tm1p.xla","server","user" and "password" strings to your own settings.

----THIS WORKBOOK------------------
Private Sub workbook_open()

'load TM1 add-in if the TM1 menu do not exist
If Not (bCommandBarExists("TM&1")) Then
Workbooks.Open ("\\path\to\tm1p.xla")
End If

'hide TM1 toolbars
On Error Resume Next
With Application
.CommandBars("TM1 Servers").Visible = False
.CommandBars("TM1 Developer").Visible = False
.CommandBars("TM1 Spreading").Visible = False
.CommandBars("TM1 Standard").Visible = False
End With
On Error GoTo 0

msg = Run("n_connect", "server", "user", "password")
If msg <> "" Then
MsgBox msg
End If

Application.Run "TM1RECALC"
End Sub

-----MODULE 1----------------------
Function bCommandBarExists(sCmdBarName As String) As Boolean

Dim bCbExists As Boolean

Dim cb As CommandBar

bCbExists = False
For Each cb In Application.CommandBars
If cb.name = sCmdBarName Then
bCbExists = True
Exit For
End If
Next

bCommandBarExists = bCbExists

End Function

Sub Open_SE()

Application.Run "TM1StartOrionWithAutomation"

'wait for Server Explorer to open
Application.Wait Now + TimeValue("00:00:05")
'expand Applications
SendKeys "{RIGHT}{RIGHT}{RIGHT}{RIGHT}{RIGHT}"
'jump to cubes and expand
SendKeys "{C}{RIGHT}"
End Sub

VBA function to check if a user is already logged in

Function IsUserLoggedIn(UserName As String, _
Servername As String) As Variant

IsUserLoggedIn = _
Application.Run("DBRW", Servername & "}ClientProperties" _
, UserName, "STATUS")

End Function

You can then use that in a sub as shown below:

Sub CheckWhetherUserIsLoggedIn()

If IsUserLoggedIn("MyUser", "TM1:") = "ACTIVE" Then
MsgBox "User is logged into TM1."
Else
MsgBox "User is doing something more interesting."
End If

End Sub

Disabling the DEL key to forbid users from deleting DBRW formulas

----THISWORKBOOK (Code) ----------

Private Sub Workbook_Activate()
DisableDel
End Sub

Private Sub Workbook_Deactivate()
EnableDel
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
a = MsgBox("Your data have already been saved in Tm1, you don't need to save this Excel slice", vbInformation)
EnableDel
ActiveWorkbook.Close False
End Sub

--------- MODULE1 (CODE) --------------

Sub DisableDel()
Application.OnKey "{DEL}", "SendSpace"
'MsgBox "Delete Key Disable"
End Sub

Sub EnableDel()
Application.OnKey "{DEL}"
'MsgBox "Delete Key Enable"
End Sub

Sub SendSpace()
'MsgBox "Delete key not allowed. Sending a space instead"
SendKeys " ~"
End Sub

Undocumented TM1 macros

TM1RECALC1 : same as shift-F9, refreshes only the active worksheet
TM1RECALC : same as F9, refreshes ALL open workbooks
TM1REFRESH : same as Alt F9, rebuilds (dynamic spreadsheets) and refreshes ALL open workbooks
TM1StartOrionWithAutomation : opens Server Explorer
CUBES_BROWSE : opens Server Explorer
SUBDELETE: deletes a subset (if unused) ex: Application.Run("SUBDELETE", "myserver:account", "MySubset")
TM1InsertViewControl : starts In Spreadsheet browser
TWHELP : opens TM1 perspectives help
TWDEFAULTS : opens TM1 Options menu
TWMERUL : opens rules worksheets menu
TWMEDIM : opens dimensions worksheets menu
to be continued...

Excel formulas references

When editing a cell formula (F2), you can easily toggle between relative and absolute references with the F4 key: $B$10 -[F4]-> B$10 -[F4]-> $B10 -[F4]-> B10