wimgielis.com

Copy data to a cube viewer

• Feb. 14, 2010

	Example files with this article:
	Copy data to a cube viewer

Input of data in TM1

As can be expected, there are many ways to input data in TM1. Just a few methods are mentioned here:

• inputing data manually in a Cube viewer, either in TM1 Client, Architect, Perspectives or Web
• copying data to a Cube viewer (as above)
• inputing data in a cell that holds a DBRW or DBR formula, either in MS Excel or in TM1 Web
• using a Turbo Integrator process (could also be executed in TM1 Web using the navigation tree or with the help of an action button)
• using DBSW-like formulas in MS Excel
• using the VBA couterpart of the DBSW formula (this is exposed through the use of tM1 Perspectives)
• using the TM1 API in for instance VB, VBA or C++
• using IBM Cognos Executive Viewer / Advisor
• using SendKeys or the Clipboard in MS Excel VBA-code
• and so on…

VBA code

Here is an example of SendKeys to send Ctrl + V (the Paste shortcut) to a Cube viewer. Open a view in a cube viewer (for instance using Architect) and put your cursor on a cell where you would want to copy a bunch of cells from Excel.

Sub CopyToTM1_1()
' Wim Gielis
' https://www.wimgielis.com
'''''
' SendKeys towards TM1 Cube viewer
' 11/07/09
'''''
    'copy the desired range
    [A1].Copy
    'or: Selection.Copy,
    'or: [B2].CurrentRegion.Copy,
    'or: refer to another sheet,
    'or: make it even more dynamic

    'insert the window caption of your cube view
    AppActivate "Cube Viewer: SERVER NAME->CUBE NAME->VIEW NAME"

    'paste copied data (mimick Ctrl + V)
    Application.SendKeys "^v"

    'go back
    AppActivate Application.Caption

    'remove the ants trace
    Application.CutCopyMode = False

End Sub

Clipboard

If you prefer the Windows Clipboard, code is shown below. If you use the clipboard in VBA, add a reference to Microsoft Forms 2.0 Object Library (see Tools > References, it is on top of the list usually). For comments, see above.

Sub CopyToTM1_2()
' Wim Gielis
' https://www.wimgielis.com
'''''
' Send data to the Clipboard and paste in a TM1 Cube viewer
' 11/07/09
'''''
    Dim DataObj As New MSForms.DataObject
    DataObj.SetText 1000
    DataObj.PutInClipboard
    AppActivate "Cube Viewer: SERVER NAME->CUBE NAME->VIEW NAME"
    Application.SendKeys "^v"
    AppActivate Application.Caption

End Sub

You do not have to use a reference and so-called early binding. With late binding, you can skip the reference. The same code would look like:

Sub CopyToTM1_2_Late_Binding()
' Wim Gielis
' https://www.wimgielis.com
'''''
' Send data to the Clipboard and paste in a TM1 Cube viewer
' 11/07/09
'''''
    With GetObject("New:{1C3B4210-F441-11CE-B9EA-00AA006B1A69}")
        .SetText 1000
        .PutInClipboard
    End With

    AppActivate "Cube Viewer: SERVER NAME->CUBE NAME->VIEW NAME"
    Application.SendKeys "^v"
    AppActivate Application.Caption

End Sub

Even simpler, back this code into a loop for instance:

Sub CopyToTM1_3()
' Wim Gielis
' https://www.wimgielis.com
'''''
' Simple way to send data to a TM1 Cube viewer
' 11/07/09
'''''
    AppActivate "Cube Viewer: SERVER NAME->CUBE NAME->VIEW NAME"
    Application.SendKeys "1000"
    AppActivate Application.Caption

End Sub

Alternatively, Save the file on top of the page.

In VBA, fixed text strings like "Cube Viewer: SERVER NAME->CUBE NAME->VIEW NAME" could be generalized easily. To let Excel VBA open a Cube viewer, check out this link.

• « Previous TM1 article
• Next TM1 article »