Creating a VBA Refresh Macro in Smart View for Oracle

excel vba hyperion epm
user2674605 picture user2674605 · Aug 12, 2013 · Viewed 36.9k times · Source

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

Jim Hervier picture Jim Hervier · May 13, 2015

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

Related questions

Is there a way to crack the password on an Excel VBA Project?

I've been asked to update some Excel 2003 macros, but the VBA projects are password protected, and it seems there's a lack of documentation... no-one knows the passwords. Is there a way of removing or cracking the password on a VBA …

Read More
How do I declare a global variable in VBA?

I wrote the following code: Function find_results_idle() Public iRaw As Integer Public iColumn As Integer iRaw = 1 iColumn = 1 And I get the error message: "invalid attribute in Sub or Function" Do you know what I did wrong? I tried …

Read More
How to use Regular Expressions (Regex) in Microsoft Excel both in-cell and loops

How can I use regular expressions in Excel and take advantage of Excel's powerful grid-like setup for data manipulation? In-cell function to return a matched pattern or replaced value in a string. Sub to loop through a column of data …

Read More