How to refresh/load RTD Bloomberg function (BDH) in excel in vba
Solution 1
I did a searching of the keyword "refresh" in the xla by opening it in notepad. Found the following targets:
RefreshAllWorkbooks
blpmain.xla!RefreshAllStaticData
blpmain.xla!'RefreshWorkbookFundamentalsData
blp.xla!IsBlpRefreshAvailable
I tried them out one by one, the first 2 works by calling:
Application.run "RefreshAllWorkbooks"
Application.run "RefreshAllStaticData"
But not calling them alone ( I guess it's because I somehow can call protected PUBLIC procedure using Application.run)
RefreshAllWorkbooks
or
RefreshAllStaticData
Thanks for all the help
Solution 2
I recently received this answer from bbg chat. I think this is what we are all looking for...
bbg helpdesk: Normally we don''t provide help on VBA on the Help Desk but I have found the below. You can use the following VBA commands to refresh BDx() formulas:
Refresh based on default option setting: Application.Run "RefreshData"
Refresh current selection: Application.Run "RefreshCurrentSelection"
Refresh current worksheet: Application.Run "RefreshEntireWorksheet"
Refresh current workbook: Application.Run "RefreshEntireWorkbook"
Refresh all workbooks: Application.Run "RefreshAllWorkbooks"
Note: When using VBA macros to refresh Bloomberg formulas, the formulas cannot complete requesting data while the macro that triggered the refresh is running. You must use Application.OnTime() to schedule a second function to run after the sub-routine that triggered the refresh exits. The following code snippet demonstrates the VBA code to refresh all workbooks, followed by a 10 second delay before calling the processSheet sub-routine:
Sub refreshSheet()
Application.Run "RefreshEntireWorksheet"
Application.OnTime (Now + TimeValue("00:00:10")), "processSheet"
End Sub
Sub processSheet()
' perform processing here
End Sub
Solution 3
I have never managed to do what you ask for. The only reliable way I have found to get up-to-date data is by calling the API directly from VBA with BLP_DATA_CTRLLib.BlpData
, waiting for the answer, and putting the result into a sheet.
With regards to opening password protected VBA code, a google or stackoverflow search should give you your answer.
Solution 4
I've found that changing something in the BDH formula would cause a refresh. Find and replace the =
sign would do the tick.
Public Sub Recalc()
Dim ws As Worksheet, FormulaCells As Range, c As Range
Application.Calculation = xlCalculationManual
For Each ws In ThisWorkbook.Worksheets
On Error Resume Next
ws.Activate
Set FormulaCells = ws.UsedRange.SpecialCells(xlCellTypeFormulas).Cells
If Err = 0 Then
For Each c In FormulaCells
c.Formula = Replace(c.Formula, "=", "=")
Next 'c
Else
Err.Clear
End If
Next 'ws
Application.Calculation = xlCalculationAutomatic
End Sub
Solution 5
This works for me:
WS.Select
WS.Range("A5").Select 'the cell that contains the BDH function
Application.Run "RefreshCurrentSelection"
Larry
I learnt a lot from SO, I'm trying to give something back. Interested in:C#, Javascript Using: VB(A/Script), SQL
Updated on July 09, 2022Comments
-
Larry almost 2 years
I would like to know if there's a way in VBA code forcing the bloomberg functions (In spreadsheet) to update its value( Any BDH functions)
Targeting Developers have faced similar issue/ have Bloomberg terminal
What have I tried--
Application.RTD.RefreshData Application.RTD.throttleInterval = 0 Application.CalculateFull
The BDH function do not reload themselve.
The only way I can refresh them now is : I click the "Refresh WorkBook" Button on the Ribbon of the BloomBerg add-in.
Since the Bloomberg Add-in is locked in VBE, I cannot find out the necessary code. Am I missing any Bloomberg Reference? Can any Bloomberg expert/user point me in the right direction? Thanks.