Wait for links- function update

[confelm.land]

Hello Everyone,

I am having a problem with the updating of functions in my code:

my code:

Public MyDate
Public MyExDate
Public MyNewDate
Public Period As String
Public ExPeriod As String
Public sht As Worksheet
Public MyPath
Public Index As Integer
Public IndexValue As String
Public waittime As Boolean
--------------------------------
Sub BBGMacro()


MyDate = DateSerial(2010, 7, 20)

For i = 0 To 1
MyNewDate = MyDate - (i)
MyExDate = MyDate - (i) - 1
Period = MyNewDate
ExPeriod = MyExDate
Index = -(i) - 1
IndexValue = Index
MyPath = ThisWorkbook.Path


Application.Run Replacements

Application.Run WaitRefreshSheet()

Application.Run SplitSave


Next i

Application.DisplayAlerts = False
Application.Workbooks.Close

End Sub
------------------------------

Sub Replacements()



For Each sht In ThisWorkbook.Sheets
Rows("4:4").Select
Selection.Replace What:=Period, Replacement:=ExPeriod, LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False
Next sht

End Sub
-----------------------------------------------------
Sub WaitRefreshSheet()

Application.Wait (Now + TimeValue("00:00:30"))

End Sub
--------------------------------------

Sub SplitSave()


For Each sht In ThisWorkbook.Sheets

sht.Copy
ActiveSheet.Cells.Copy
ActiveSheet.Cells.PasteSpecial Paste:=xlPasteValues
ActiveSheet.Cells.PasteSpecial Paste:=xlPasteFormats
ActiveWorkbook.SaveAs _
Filename:=MyPath & "\" & "t" & IndexValue & "\" & sht.Name & ".csv", FileFormat:=xlCSV
ActiveWorkbook.Close savechanges = False

Next sht
End Sub
-------------------------
The code basically controls a process though updating a bloomberg function.
The main macro bbg refers to loops on the requested dates.
During each loop the macro "replacements" is called where it replaces the correct dates in formulas stored on cell A4 for 218 sheets of the workbook
When the replacement is made i have to make the code to wait until links are updated (data are retrieved) and then move to the next subrutine to split and save each sheet. Normally after saving the code will move to next loop (next i).
The problem is that i can't make the code wait for updates-calculations
It seems though that the wait function does not help because all application freezes even the updating. i even tried calling the Splitsave macro of my code with the OnTime method, but it did not worked properly.
Any help? I am stuck with this over 3 days now!!!!

 

[Andrew Poulsom]

Have you tried setting calculation to manual and calling the Calculate method when needed? VBA should wait for the calculation to finish before continuing, although I'm not sure about Bloomberg functions.

 

[confelm.land]

No, it didn't worked whenever the dates are replaced the process where retrieving the results cannot be controlled.

 

[Andrew Poulsom]

I see that the Bollmberg functions are asynchronous. This may help you:

http://www.experts-exchange.com/Programming/Languages/Visual_Basic/Q_22565058.html

 

[confelm.land]

Thanks but i can't have access to this solution

 

[Andrew Poulsom]

Google for "bloomberg function wait excel" (no quotes). It's towards the bottom of page 1 entitled:

Using excel with Bloomberg BLP functions : bloomberg, Excel, blp

If you click the link you should be able to see the replies by scrolling down.