Wait for links- function update

confelm.land

New Member
Joined
Jul 21, 2010
Messages
6
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!!!!:(
 
Last edited:

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
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

New Member
Joined
Jul 21, 2010
Messages
6
No, it didn't worked whenever the dates are replaced the process where retrieving the results cannot be controlled.
 

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
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.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,797
Messages
5,833,738
Members
430,228
Latest member
Normano

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top