Forum Stats

  • 3,855,036 Users
  • 2,264,452 Discussions


SmartView 11 - Excel 2002 VBA Problem with HFM-Function "HypRetrieve"

.Martin. Member Posts: 59
edited Feb 26, 2010 10:18AM in Financial Consolidation
Hello all,

i hope someone can help me.

I have a complex macro wich refreshes a excel report and print it to pdf. After this, all printed pdf documents will be merged to pdf-books for different report receiver.

In order to write the macro i used the SmartView Admin Guide to get all information how i can use the HypRetrieve VBA function.

I run my macro step by step and i can see that HypRetrieve will be called and returns no error message and return code 0. So the refresh should be worked fine, but it isn´t so.

Although the return code is 0 i didn´t get the right values. In the cells with my hsgetvalue functions i get a zero as value. If i click on refresh the sheet will be refreshed correctly but if i call the HypRetrieve function from my macro it doesn´t work.

I´ll be at a loss. It looks like work fine but it isn´t so. Without any error message or other return code i don´t know where is my failure.
Hope someone has an idea.

Many thanks in advance for every idea or help.

Best wishes


  • Erich Ranz
    Erich Ranz Member Posts: 570
    edited Feb 26, 2010 8:45AM
    If the only part of the macro that's giving you trouble is the data retrieve part, you may try the following-
    Dim oBar As CommandBar
    Set oBar = Application.CommandBars("Worksheet Menu Bar")
    oBar.Controls("Hyperion").Controls("Refresh All").Execute
    If you need to refresh just one sheet, make that sheet active and change the code from "Refresh All" to "Refresh".
  • .Martin.
    .Martin. Member Posts: 59
    Hello Henson12,

    thanks for your answer. I tried it and it doesn´t work. No error message, it looks like it works but the values are 0.

    I build a test sheet with the following POV i wrote in the non printable range:

    A B
    1 Connection: DE09KER
    2 Scenario#IST
    3 Year#2009
    4 Period#P04
    5 Account#K0
    6 View#<Scenario View>
    7 Custom1#[None]
    8 Custom2#[None]
    9 Custom3#[None]
    10 Custom4#[None]
    11 Entity#

    My test reporting row looks like:

    13 DE_2200 0

    my hsgetvalue: =hsgetvalue($B$1;$B$2;$B$3;$B$4;$B$5;$B$6;$B$7;$B$8;$B$9;$B$10;$B$11&$B13)

    If i refresh manually with klick on Refresh i get the values from my application but if i refresh with my macro i get a 0.

    I wrote another macro with HypRetrieve and that works fine, but in the complex macro with printout as pdf it doesn´t work. I don´t know why.
  • Erich Ranz
    Erich Ranz Member Posts: 570
    I'm using Excel 2007. That may be the difference. Did you try the VBA code just using "Refresh"? Not sure if there's a "Refresh All" option in 2002.
  • .Martin.
    .Martin. Member Posts: 59
    You´re right, there is a "Refresh All" option in 2002 and i tried it with this in my macro, too but it doesn´t work.

    I tried it with automatic and manually calculation option in excel but both didn´t work.

    The crazy thing is that in my standalone makro it works fine but i need it in my create pdf makro and there it doesn´t work although i use the same code.
This discussion has been closed.