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

i have following code works charm when step-through line-by-line (or when step through 1 or 2 loop iterations fire rest). when run button code not work in sense hyperion retrieve never updated each iteration of department change. department gets changed correctly (as can seen excel , resulting pdf file).

so in nutshell, code works no seen or trapped errors, result set of pdfs identical data, though labeled different departments when run button press.

i have scoured net quite bit, tried using doevents , application.wait no success. have ideas how ensure refresh happens each loop iteration when running button press?

option explicit  declare function hypmenuvrefresh lib "hsaddin.dll" () long  sub createallpdfs()  '... setup code declare variables , loop range ...   'loop through departments dim cel range each cel in rngloop 'rngloop declared , set in setup code      'set department on drivers tab     wsdrivers.range("b4").value = "'" & cel.value      '*** --> tried wait before loop (just shot in dark type thing)     'application.wait (now + timevalue("00:00:02")) 'pauses 10 seconds, adjust needed      'refresh hyperion     dim lngreturn long     lngreturn = hypmenuvrefresh()      ' *** --> tried events     'doevents     '*** --> tried wait after loop     'application.wait (now + timevalue("00:00:02")) 'pauses 10 seconds, adjust needed          'quick error check         if lngreturn <> 0             msgbox "could not refresh!"             exit sub         end if      'save pdf     wspl.exportasfixedformat xltypepdf, cel.offset(, 1) & "\" & cel.offset(, 2) & ".pdf", , , , , , false  next  end sub 

we refer online documentation hypmenuvreresh stated

hypmenuvrefresh() retrieves data active sheet, , places data @ beginning of active worksheet.

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

hence solution found activating target worksheet (i.e. wspl.activate in case) right before executing hypmenuvreresh.

so, generally, practice activate target worksheet before hypmenuvrefresh because hypmenuvrefresh not throw error if refreshing worksheet not connected hyperion cube. additionally, hypconnected() can declared check if sheet connected hyperion cube (i.e. hyperion refresh-able) before refreshing it.

hypconnected() returns true value if sheet connected provider , returns false value if sheet not connected.