CODEWITHSUNDEEP
excelvbabloomberg
YoYue
YoYue

Reputation: 41

Excel Links(With some bloomberg function as input) not updated until VBA finished

I have an excel input workbook A has some inputs based on internal worksheets references and bloomberg function plus some calculation fields.

I have an excel output workbook B that needs to retrieve the information from A, however, if I I open workbook A within B's vba, it wont update the links infor until the VBA finish calling.

example of A's input, A contains sheet1, sheet2, sheet3 I want a cells from sheet1 cells(1,1), the content of cells(1,1) mite be sheets2!cells(2,1), this sheets2!cells(2,1) mite be a calculation field based on reference from another tab or some bloomberg function call input.

Is there way that I can validate all the links within workbook A first, so that my workbook B could retrieve all the updated information.

I have tried

Application.Workbooks.Open FileName:=path_array(tmp), UpdateLinks:=3       
Application.Calculate
Application.RTD.ThrottleInterval = 0
Application.Run "RefreshAllStaticData"
Application.Run "ConnectChartEvents"
Application.Run "RefreshData"
Application.Run "RefreshEntireWorkBook"
DoEvents

VBA Output looks like this:
A      B      C  D  E   F   G   H   I   J
4.4%  2.1%  2.2%    #VALUE! #VALUE! #VALUE! #VALUE! 11  5   #VALUE!
Manual like this:
A      B      C  D  E   F   G   H   I   J
4.4%  2.1%  2.2%    3.1%    4.2%    5.5%    3.4%    11  5   8.0%

to force some of the calculation go thru, its updating using VBA but will return #Value! for the fields that have extra layer of reference. If I open it just manually, everything auto updated less than a second. Have been struggling why there are such difference. Is there way that I clock VBA until this input is updated. Thanks in advance

Upvotes: 0

Views: 3190

Answers (1)

AndASM
AndASM

Reputation: 10428

I'm not familiar with bloomberg but here are three things.

You can delay execution in workbook B with Application.OnTime. Load workbook A, then call a second sub in workbook B using Application.OnTime with a second or two delay, this should allow workbook A to run whatever code it runs on loading.

Application.RTD.RefreshData might help.

More specific to your question, do the answers here help? How to refresh/load RTD Bloomberg function (BDH) in excel in vba

Upvotes: 2

Related Questions