Using Perspectives and VBA to change TM1 cube values & calc?

[jim2011]

Here's my situation:

I'm using Perspectives' in spreadsheet browser to open a cube view in Excel. This cube has a simple calculation, Price x Quantity = Total Cost. I am trying to change the cell that contains quantity (B6) in the cube with the use of VBA and then capture the Total Cost. (B8) I'm just using a simple For loop to change the value from 1 through 10 with variable x. In this loop, I have the VBA set the cell containing quantity equal to x, then I call the TM1RECALC function, then I copy what the Quantity and Total Cost were to other cells. The problem is if I do it a few times, it usually runs with no errors but the Total Cost value is always what the starting value was. If I change the loop to run 100 times instead of 10, I get multiple tm1xl "internal application error" messages. Here is my VBA macro:



Sub test()
Dim x As Integer
For x = 1 To 10
Range("B6").Value = x
Application.Run "TM1RECALC"
Cells(12 + x, 1) = Range("B6").Value
Cells(12 + x, 2) = Range("B8").Value

Next x
End Sub


Anyone have experience doing something similar or know what might be the culprit? Thanks!

[tomok]

I don't use the in spreadsheet browser much because 1) it wasn't very useful and 2) because it's no longer supported. That being said, I'm pretty sure you can't do what you are trying with it. Setting the cell that contains the quantity to something via VBA does not WRITE that value back to TM1 so endlessly looping through and doing a TM1RECALC is never going to return anything other than the original value. Skip the in spreadsheet browser and use a combination of DBR and DBS. Have the DBR pull back the value from the cube (you can use cell B6) and have the DBS send a value from B7.

B6 - contains a DBS for sending Quantity to the cube
B7 - contains the actual quantity value you want to poke into the cube
B8 - contains a DBR that pulls back Total Cost from the cube

Code: Select all

Sub test()
Dim x As Integer
For x = 1 To 10
  Range("B6").Value = x
  Application.Run "TM1RECALC"
  Cells(12 + x, 1) = Range("B6").Value
  Cells(12 + x, 2) = Range("B8").Value
Next x
End Sub

[jim2011]

Thank You sooooo much Tom! Using your suggestions, I got everything working.