CODEWITHSUNDEEP
excelvbahyperion
user2674605
user2674605

Reputation: 103

Creating a VBA Refresh Macro in Smart View for Oracle

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!

Upvotes: 2

Views: 44875

Answers (6)

Imperator
Imperator

Reputation: 1

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

Upvotes: 0

Jim Hervier
Jim Hervier

Reputation: 21

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

Upvotes: 2

user2836536
user2836536

Reputation: 1

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

Upvotes: 0

Mr.Monshaw
Mr.Monshaw

Reputation: 450

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?

Upvotes: 1

joao
joao

Reputation: 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

Upvotes: 0

Charles Beyer
Charles Beyer

Reputation: 11

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'

Upvotes: 1

Related Questions