Refresh the BDH's after updating sheet. Bloomb

Question

I see there's some old stuff about this, but i'm hoping someone has found a new solution.

with my routine, I update the dates in 3 workbooks, so that the BDH commands get the latest prices, then i copy the results from those 3 workbooks into a separate summary workbook. However, as common apparently, The BDH function doesn't refresh/update, even after a timepause in there.

What are the latest findings on refreshing or re-requesting the BDH commands before i Copy paste them to the 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 As Range

For Each c In Selection.Cells
    If c.Value = "#N/A Requesting Data..." Then
        Call Application.OnTime(Now + TimeValue("00:00:01"), "ProcessData")
        Exit Sub
    End If
Next c

Call CopyPaste

End Sub

Answer 1

You need to use Application.OnTime to achieve this. Bloomberg formulas will not update while a macro is paused. There are examples in the FAQ section on WAPI on Bloomberg. The below is taken from there where you will also find an example spreadsheet.

---

[Download Example! Download a working Excel VBA example here] The data returned by any of our BDx() family of functions is in an asynchronous manner. Therefore, if your Excel VBA application [macro] is dependent upon returned Bloomberg data, we recommend that you incorporate the following logic into your VBA code that will, essentially, pause your application while the data is populated in your 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 i

For i = 2 To MAXROWS

' Check to make sure that the cells are NOT requesting data 
If "#N/A Requesting Data..." = Cells(i, 2) Then

Application.OnTime (Now + TimeValue("00:00:02")), "checkStatus"
Exit Sub

End If

Next i

MsgBox "We just finished getting the values"
' Add code here that would process the data now that it's updated

End Sub

The above code can be added to a blank module and the following code will be added to the click event handler, for instance, of a button on the worksheet: fillFormula