This content has been marked as final. Show 5 replies
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
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.
'Attempt to call the Hyperion Refresh utility
x = HypMenuVRefreshAll()
If x = 0 Then
MsgBox ("Hyperion Refresh call failed. Err=" & x)
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.
Edited by: beyerch2 on Feb 13, 2013 9:10 AM