Creating a VBA Refresh Macro in Smart View for Oracle

Question

Does anyone know the VBA Code that I need to use so that I can automatically “Refresh” and “Refresh All” using EPM (Hyperion) Smartiew? The “Refresh” function pulls the data into Excel on the active tab where the “Refresh” all function refreshes all tabs in the Workbook.

I’d like to create a simple macro attached to a command button in Excel and I’m not sure which VBA code to use.

I tried recording a macro where by I simply starting recording clicked refresh and stop recording although this did not work.

I tried this code just for the refresh:

Declare Function HypMenuVRefresh Lib "HsAddin.dll"() As Long

Sub MRetrieve()
  X = HypMenuVRefresh()
End Sub

But received an error message saying that I had to update the declare method for use with a 64 bit system (I am using a 64 bit system).

Does anyone know how I could create this automatic Macro to refresh the data?

Any help would be much appreciated!

Answer 1

Sub Refresh()
    '
    ' Refresh Macro
    ' Macro recorded 8/12/2011 by joao-oliveira
    '
    Dim oBar As CommandBar
    Set oBar = Application.CommandBars("Worksheet Menu Bar")
    oBar.Controls("Hyperion").Controls("Refresh").Execute
End Sub

Answer 2

HypRetrieveRange can refresh or update a range of information, there are also a number of other functions that might suit what you want depending on how much information you need to refresh. Did you import the entire smartview.bas file like they recommended?

Answer 3

Create a button and assign it a new subroutine. Use the call command to call the public function.

Sub RefreshHFM()
'
' RefreshHFM Macro
'
    Call HypMenuVRefreshAll
'
End Sub

Answer 4

This worked for me. You'll be able to assign this macro to any button. Instead of using the refresh all function, I am using the HypMenuVRefresh function within each worksheet.

Sub refreshWS()
Dim Count, i As Integer

 i = 1
 Count = Worksheets.Count

    Do While i < Count

     Sheets(i).Select
     Call HypMenuVRefresh
     i = i + 1

    Loop

    MsgBox "done"

End Sub

Answer 5

The declaration for x64 in VBA is not correct.

Try:

Private Declare PtrSafe Function HypMenuVRefresh Lib "HsAddin" () As Long

Sub refreshWS()

    Dim Count, i As Integer

    i = 1

    Count = Worksheets.Count

    Do While i <= Count

        Sheets(i).Select

        MsgBox Sheets(i).Name

        Call HypMenuVRefresh

        i = i + 1

    Loop

    MsgBox "done"

End Sub

Answer 6

Use the function calls that basically simulate pressing the buttons!

Refresh current worksheet

Declare Function HypMenuVRefresh Lib "HsAddin.dll" () As Long  
lngReturn = HypMenuVRefresh()

Refresh All Worksheets

Declare Function HypMenuVRefreshAll Lib "HsAddin.dll" () As Long  
lngReturn = HypMenuVRefreshAll()

*NOTE : Return value of 0 is 'OK'