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

Question

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

Nope, will that help? The big confusion I am getting is why there is a difference betweeen manual and vba automated open it. I will give a try — YoYue, Aug 27 '13 at 19:05
Hmm, it's probably updating asynchronously and just takes a few seconds to finish and recalculate. — RBarryYoung, Aug 27 '13 at 19:19
Hi RBarryYoung, do u know if there is way that I can force it back to synchronously when using VBA — YoYue, Aug 27 '13 at 19:25
No, I've only used Bloomberg once. I'd check the Bloomberg tab though to see if there are any options like that. — RBarryYoung, Aug 27 '13 at 19:29
possible duplicate of [Bloomberg data doesn't populate until Excel VBA macro finishes](http://stackoverflow.com/questions/8669845/bloomberg-data-doesnt-populate-until-excel-vba-macro-finishes) — assylias, Aug 28 '13 at 17:38

score 2 · Answer 1 · edited May 23 '17 at 11:49

2

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

edited May 23 '17 at 11:49

Community

1
1

answered Aug 27 '13 at 16:35

AndASM

9,458
1
21
33

Hi AndASM, thanks for the reply. The problem is the input workbook A, when manual open it, it takes less than a sec to update. When open it from workbook B uisng vba, put a on.time 2 seconds delay, its not updating. only after the vba finishes. – YoYue Aug 27 '13 at 16:54
@YoYue Your best bet is probably to retrieve the data using the VBA API rather than formulae. The formulae won't update as long as your VBA code is running I think. – assylias Aug 27 '13 at 21:17
@assylias the problem is that I dont own that input sheet, the mapping logic is really messy with 20+ worksheets workbook. If I repopulate everything into VBA API, thats not sth doable in a short time period. Do u know if there is any workaround of this. you are probly right, the formula update is served as whenever the excel is free or update every 2 seconds by default, if currently VBA is running the formula will frozen and wait until the current excel is finished. I am assuming there exist a bloomberg function that can force such an update occurs – YoYue Aug 27 '13 at 22:29

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

1 Answers1