Copy data to a cube viewer
- Feb. 14, 2010
|Example files with this article:|
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…
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()'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 = FalseEnd Sub
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()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()End Sub
Even simpler, back this code into a loop for instance:
Sub CopyToTM1_3()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.