5 Replies Latest reply: Feb 14, 2013 11:01 AM by kjstr2007 RSS

    How can I refresh Smart View using a Macro

    kjstr2007
      Can anyone help me, I am trying to create a macro that will refresh a Smart View sheet. I recorded a simple macro but it doesn't seem to work.

      Any help would be appreciated

      Thanks!
        • 1. Re: How can I refresh Smart View using a Macro
          beyerch2
          Here is some "quick and dirty" code to do this .....

          What is this quick and dirty code doing?

          I had a file that was sent out to end users for some forecast data capture. There is a work sheet that has a data entry form and a worksheet that has the Smartview retrieve on it. (which is hidden)

          On the data capture screen, I added a big button which executes the smartview refresh. While the end user should be able to click on the Smartview Refresh button on the toolbar/ribbonbar, I wanted to make it dirt simple for them to navigate... When the button is clicked, I switch to the Smartview worksheet, execute the Smartview Refresh method, and then switch back to the input worksheet. In order to make this worksheet switching transparent to the end user, I instruct Excel to disable screen updating. (and re-enable after I'm done refreshing)

          While you can't use this code plug and play due to my hardcoding and specific purpose, it illustrates the how to Refresh in a macro. It's pretty straight forward!

          Dim x
          
          'Disable screen updating temporarily so users don't see the sheet movement
          Application.ScreenUpdating = False
          
          'Switch to the Hyperion Data sheet
          ThisWorkbook.Worksheets("Hyperion_Data").Activate
          
          'Attempt to call the Hyperion Refresh utility
          x = HypMenuVRefresh()
          If x <> 0 Then
            MsgBox ("Hyperion Refresh call failed.  Err=" & x)
          End If
          
          'Switch back to the original sheet that called the refresh
          ThisWorkbook.Worksheets("Summary Forecast").Activate
          
          'Enable screen updating again as we are done swapping sheets.
          Application.ScreenUpdating = True
          • 2. Re: How can I refresh Smart View using a Macro
            Erich Ranz
            Also might want to add this line of code outside your sub:
            Public Declare Function HypMenuVRefresh Lib "HsAddin" () As Long
            • 3. Re: How can I refresh Smart View using a Macro
              kjstr2007
              Thanks beyerch2! I simplified your script to the following. The Worksheets are refreshing but when it completes, I get the following error

              Hyperion Refresh Call failed. Err=0

              Also note I add an equals sign where it says If x = 0 Then (I assume that was just a typo)

              Also, Do I need to import the smartview.bas into a VB editor module? In chapter 16 of the Smart View admin manual, it says after doing this I can copy and paste VBA functions from it, but after doing so I cannot find the functions.

              Dim x

              'Attempt to call the Hyperion Refresh utility
              x = HypMenuVRefreshAll()
              If x = 0 Then
              MsgBox ("Hyperion Refresh call failed. Err=" & x)
              End If

              End Sub
              • 4. Re: How can I refresh Smart View using a Macro
                beyerch2
                Erich - Oops, he'll probably want that..... :-)

                OP - The statement should be "if x NOT EQUAL to 0". Apparently Oracle forum doesn't like a greater than and less than sign next to each other. 0 means No Error.

                - I also have some more info in this power point... The smartview stuff is near the end.

                https://www.box.com/s/dadff30a8091924a589d

                Edited by: beyerch2 on Feb 13, 2013 9:10 AM
                • 5. Re: How can I refresh Smart View using a Macro
                  kjstr2007
                  Thank you very much Charles, the macro is working! I appreciate your help!

                  Charles, Erich, Can you tell me what the following command is for?

                  Public Declare Function HypMenuVRefresh Lib "HsAddin" () As Long