[Excel] TM1 Get Value from Cube VBA

Post Reply
HighKeys
Posts: 44
Joined: Fri Aug 09, 2019 10:11 am
OLAP Product: TM1 / TM1 Web / Perspectives
Version: Planning Analytics V2.0.8
Excel Version: Plus 2013

[Excel] TM1 Get Value from Cube VBA

Post by HighKeys » Tue Aug 27, 2019 8:52 am

Hello,

how can i get a value from a cube in VBA?

I tried to send the DBR Formula via "Application.Run" but it seems not to work.

Code: Select all

Function GetNumberFromTM1(Server As String, Cube As String, ParamArray arg() As Variant) As Long

    ReDim Z(UBound(arg()))
    For i = 0 To UBound(arg())
        Z(i) = arg(i)
    Next i
    
    send = "DBR(" & Chr(34) & Server & ":" & Cube & Chr(34) & ";"
    
    For i = 0 To UBound(Z())
        
        'Gerade Param elemente
       ' If i Mod 2 = 0 Then
            send = send & Chr(34) & Z(i) & Chr(34) & ";"
      '  Else
            
      '  End If
        
    Next i

    GetNumberFormTM1 = Application.Run(send)

End Function
Thanks for your help!

User avatar
Alan Kirk
Site Admin
Posts: 6011
Joined: Sun May 11, 2008 2:30 am
OLAP Product: TM1
Version: PA2 Classic (PAW-free zone)
Excel Version: 2010 and 2016
Location: Sydney, Australia
Contact:

Re: [Excel] TM1 Get Value from Cube VBA

Post by Alan Kirk » Tue Aug 27, 2019 9:00 am

You don't put the arguments to DBR in brackets like that when you are using Application.Run. DBR is the first argument (in quotes), and the other arguments are separated by commas.
"To them, equipment failure is terrifying. To me, it’s 'Tuesday.' "
-----------
Before posting, please check the documentation, the FAQ, the Search function and FOR THE LOVE OF GLUB the Request Guidelines.

HighKeys
Posts: 44
Joined: Fri Aug 09, 2019 10:11 am
OLAP Product: TM1 / TM1 Web / Perspectives
Version: Planning Analytics V2.0.8
Excel Version: Plus 2013

Re: [Excel] TM1 Get Value from Cube VBA

Post by HighKeys » Tue Aug 27, 2019 11:04 am

Hello,

thanks! i was too deep in the formula when i coded this...

Code: Select all

Function GetNumberFromTM1(Server As String, Cube As String, ParamArray arg() As Variant) As Long

    ReDim Z(UBound(arg()))
    For i = 0 To UBound(arg())
        Z(i) = arg(i)
    Next i
    
    send = Chr(34) & "DBR" & Chr(34) & "," & Chr(34) & Server & ":" & Cube & Chr(34) & ","
    
    For i = 0 To UBound(Z())
        

        If i = UBound(Z()) Then
            send = send & Chr(34) & Z(i) & Chr(34)
        Else
            send = send & Chr(34) & Z(i) & Chr(34) & ","
        End If
        
    Next i

    GetNumberFormTM1 = Application.Run(send)
Now i changed the formatting from "send" to the Application.Run syntax, but still it says the macro is missing.

Any idea?

Thanks you so much!


Edit:

Here is the working Code:

Code: Select all

Function GetNumberFromTM1(Server As String, Cube As String, ParamArray arg() As Variant) As Long

    ReDim Z(UBound(arg()))
    For i = 0 To UBound(arg())
        Z(i) = arg(i)
    Next i
    
    send = "DBR(" & Chr(34) & Server & ":" & Cube & Chr(34) & ","
    
    For i = 0 To UBound(Z())
        

        If i = UBound(Z()) Then
            send = send & Chr(34) & Z(i) & Chr(34) & ")"
        Else
            send = send & Chr(34) & Z(i) & Chr(34) & ","
        End If
        
    Next i

    t = Application.Evaluate(send)
    GetNumberFromTM1 = t
End Function
Thanks for the Help!

Post Reply