Copy data to a cube viewer

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 Control + 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 ' http://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 Control + 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 ' http://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 ' http://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 ' http://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.




    Homepage

    Section contents

    About Wim

    Wim Gielis is a Business Intelligence consultant and Excel expert

    Other links