Multiple Oracle Smartview Refresh Not Working in a Loop (Works When I Step Through Code)

Question

I have the following the code that works like a charm when I step-through it line-by-line (or even when I only step through 1 or 2 loop iterations then fire the rest). When I run it from a button the code does not work in the sense that the Hyperion Retrieve is never updated for each iteration of department I change. The department itself gets changed correctly (as can be seen from the Excel and resulting PDF file).

So in a nutshell, the code works with no seen or trapped errors, but the result is a set of PDFs with identical data, though labeled with different departments when run from a button press.

I have scoured the net for quite a bit, tried using DoEvents and Application.Wait to no success. Does anyone have any ideas how to ensure the Refresh happens for each loop iteration when running from a button press?

Option Explicit

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

Sub CreateAllPDFS()

'... setup code to declare variables and loop range ...


'loop through departments
Dim cel As Range
For Each cel In rngLoop 'rngLoop declared and set in setup code

    'set department on drivers tab
    wsDrivers.Range("B4").Value = "'" & cel.Value

    '*** --> tried to wait before the loop (just shot in the dark type thing)
    'Application.Wait (Now + TimeValue("00:00:02")) 'pauses for 10 seconds, adjust as needed

    'refresh hyperion
    Dim lngReturn As Long
    lngReturn = HypMenuVRefresh()

    ' *** --> tried Do Events
    'DoEvents
    '*** --> tried to wait after the loop
    'Application.Wait (Now + TimeValue("00:00:02")) 'pauses for 10 seconds, adjust as needed

        'quick error check
        If lngReturn <> 0 Then
            MsgBox "Could Not Refresh!"
            Exit Sub
        End If

    'save as pdf
    wsPL.ExportAsFixedFormat xlTypePDF, cel.Offset(, 1) & "\" & cel.Offset(, 2) & ".pdf", , , , , , False

Next

End Sub

Answer 1

We refer to the online documentation for HypMenuVReresh where it is stated that

HypMenuVRefresh() retrieves data into the active sheet, and places the data at the beginning of the active worksheet.

Source: https://docs.oracle.com/cd/E12032_01/doc/epm.921/html_hsv_user/hsv_help-13-63.htm#528899

Hence the solution is found by activating the target worksheet (i.e. wsPL.Activate in this case) right before executing HypMenuVReresh.

So, generally, it would be good practice to always activate the target worksheet before HypMenuVRefresh because HypMenuVRefresh will not throw an error if it is refreshing a worksheet that is not connected to a Hyperion cube. Additionally, HypConnected() can be declared to check if the sheet is connected to a Hyperion cube (i.e. Hyperion refresh-able) before refreshing it.

HypConnected() returns a true value if the sheet is connected to an provider and returns a false value if the sheet is not connected.