i see there's old stuff this, i'm hoping has found new solution.
with routine, update dates in 3 workbooks, bdh commands latest prices, copy results 3 workbooks separate summary workbook. however, common apparently, bdh function doesn't refresh/update, after timepause in there.
what latest findings on refreshing or re-requesting bdh commands before copy paste them results sheet?
===========
twenty.worksheets("portfolio_2016").activate [k3].value = tradeday [l3].value = prevtradeday 'japan.worksheets("portfolio_2016").activate '[k3].value = tradeday '[l3].value = prevtradeday 'aar.worksheets("portfolio_2016").activate '[k3].value = tradeday '[l3].value = prevtradeday call refreshstaticlinks end sub public sub refreshstaticlinks() call twenty.worksheets("portfolio_2016").range("k7:q26").select call application.run("refreshcurrentselection") call application.ontime(now + timevalue("00:00:01"), "processdata") end sub private sub processdata() dim c range each c in selection.cells if c.value = "#n/a requesting data..." call application.ontime(now + timevalue("00:00:01"), "processdata") exit sub end if next c call copypaste end sub
you need use application.ontime achieve this. bloomberg formulas not update while macro paused. there examples in faq section on wapi on bloomberg. below taken there find example spreadsheet.
[download example! download working excel vba example here] data returned of our bdx() family of functions in asynchronous manner. therefore, if excel vba application [macro] dependent upon returned bloomberg data, recommend incorporate following logic vba code will, essentially, pause application while data populated in excel cells:
option explicit private const maxrows = 25 sub fillformula() range("b2:b" & maxrows).clear range("b2").formula = "=bdp($a2,b$1)" range("b2").autofill range("b2:b" & maxrows), xlfilldefault checkstatus end sub sub checkstatus() dim = 2 maxrows ' check make sure cells not requesting data if "#n/a requesting data..." = cells(i, 2) application.ontime (now + timevalue("00:00:02")), "checkstatus" exit sub end if next msgbox "we finished getting values" ' add code here process data it's updated end sub the above code can added blank module , following code added click event handler, instance, of button on worksheet: fillformula
Comments
Post a Comment