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!

score 2 · Answer 1 · edited May 13 '15 at 19:04

2

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

edited May 13 '15 at 19:04

Sarah Elan

2,465
1
23
45

answered May 13 '15 at 18:24

Jim Hervier

21
2

score 1 · Answer 2 · edited Jan 15 '14 at 16:47

1

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?

edited Jan 15 '14 at 16:47

Kent Pawar

2,378
2
29
42

answered Aug 12 '13 at 16:06

Mr.Monshaw

450
2
7

Thanks for the reply. I have tried `HypRetrieveRange` and `HypRefresh` using a declare statement like this: `Declare Function HypMenuVRefresh Lib "HsAddin.dll"() As Long Sub MRetrieve() X = HypMenuVRefresh() End Sub` But these didn't work I got the same error saying: Compile Error: Constants, fixed-length string, array, user-defined types and Declare statements not allowed as Public members of object modules. Have you came across this error or know how to fix it? I think this refresh statement is possible the best way to go. – user2674605 Aug 13 '13 at 09:52

score 1 · Answer 3 · edited Jan 15 '14 at 15:10

1

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'

edited Jan 15 '14 at 15:10

Kent Pawar

2,378
2
29
42

answered Oct 10 '13 at 17:39

Charles Beyer

11
1

score 0 · Answer 4 · edited Jan 15 '14 at 15:25

0

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

edited Jan 15 '14 at 15:25

Phantômaxx

37,901
21
84
115

answered Aug 29 '13 at 14:56

joao

1

score 0 · Answer 5 · answered Jun 25 '14 at 21:39

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

score 0 · Answer 6 · answered Aug 13 '15 at 19:59

0

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

answered Aug 13 '15 at 19:59

Imperator

1
1

Creating a VBA Refresh Macro in Smart View for Oracle

6 Answers6