PALO array functions

This site uses cookies. By continuing to browse this site, you are agreeing to our Cookie Policy.

  • PALO array functions

    Hi,
    PALO array functions still seem not to work. When I use e.g. PALO.CUBE_LIST_DIMENSIONS as matrix formula in Excel Sheet I only get back the first entry like "Products". And they don't work with vba (Application.run). Has anything been changed with the usage or is it still an open issue?

    Greetings from cologne
    Holger

    The post was edited 1 time, last by h_decker ().

  • RE: PALO array functions

    Hello Holger,

    Try pasting the function into one field, drag the formular down a couple of rows so the first entry will be seen at each row. Select this area and then go to the formular bar above. Set the cursor behind the formular and press
    <CTRL>+<SHIFT>+<ENTER> so your formular should now look like this ('{', '}' get added):

    {=PALO.CUBE_LIST_DIMENSIONS('Server/Database'; 'Cube')}

    and all dimensions should be there now. At least as long as your area is, so it could be better to select the whole column.

    Regards,
    Stephanie
  • RE: PALO array functions

    hi Holger,

    I 've tried it with an array but it still didn't work.

    But you can get the values programmatically as follows:

    Sub XY

    Dim Server as String
    Dim Datenbank as String
    dim i as integer

    i = 1
    Server = "localhost"
    Datenbank = "Demo"


    'the dimensions of the database in the variable "Datenbank" are getting listed:

    Sheets("temp").Range("h1:h100").ClearContents
    Sheets("temp").Range("h1:h100").Select
    Selection = Application.Run("PALO.DATABASE_LIST_DIMENSIONS", Server & "/" & Datenbank)
    Application.SendKeys "{Enter}+{+}+{^}", True

    On Error GoTo ende
    Do While Len(Range("h" & i).value) > 0

    'do something you like

    i = i + 1
    loop
    exit sub
    ende:

    end sub


    Much better is it to avoid the selection-object and apply the range-method:


    Dim Bereich as Range
    Dim Server as String
    Dim Datenbank as String

    Server = "localhost"
    Datenbank = "Demo"

    Set Bereich = Range("H1:H100")
    Bereich = Application.Run("PALO.DATABASE_LIST_DIMENSIONS", Server & "/" & Datenbank)


    ..another example - use the comma before the last item:

    Set Bereich = ThisWorkbook.Sheets("temp").Range("A1:A30")
    Bereich = Application.Run("PALO.DIMENSION_LIST_ELEMENTS", "localhost/Demo", "Months")
    ciao, Ralf B

    The post was edited 7 times, last by Ralle002 ().

  • Hi Holger,

    thanks, your hint was very helpful because my method didn't work in some cases.

    The following code works perfectly:

    dim v as variant
    dim i as integer
    v = Application.Run("PALO.DATABASE_LIST_DIMENSIONS", "localhost/Demo")

    if isarray(v) then
    for i = 1 to ubound(v)
    select case ubound(v)
    case 1
    debug.print v(i)
    case else
    debug.print v(i,1)
    end select
    next i
    end if
    ciao, Ralf B

    The post was edited 5 times, last by Ralle002 ().