Hi,I have a problem with running my exce vba that contains bloomberg dde links.The problem goes like this.I have a macro in excel file "A" that opens up excel file "B" and then do some calculation on excel file "B".Once the calculation is being done, the macro in excel file A" will close excel file "B".But the problem is file "B" contains live bloomberg dde links. Before the dde links could get updated, the macro in file "A" does the calculation and closes file "B".I had tried using the "wait" and "sleep" function in excel vba to pause the macro and allow the dde links to get updated. But this does not work as the "wait" and "sleep" function pauses the whole excel application including the dde links updateMy question is 1, Is there a way in my code to make sure that exel updates all the dde links before doing the calculation and close the file?OR2, A way to pause the macro without interfering the updates of the dde links?Many thanks

answer to the (1) is that you can check whether the cell value where the DDE link is located reads #N/A or does it read an integer/double etc. if it is double, then you can close it. for instance, here can be a (possible) code that can be inserted just before you want to close the excel file "B" ... you can put it in the BeforeClose() subdim i as integeri=1while Isnumber(Sheets("MySheet").Cells(1,1).Value) <> 1i=i+1wendit assumes Cells(1,1) has BB links. It did work for me in a couple of automations. Let me know how if it worked with you. BTW, I used "wait" extensively too, and it worked pretty well also - it should be able to update DDE links.

Hi pb273,Thanks for the suggestion.I had alreadly try the while loop method but it did not work for me.The while loop in the macro seems to have taken over the excel application and it does not allow the de links to get updated.

One solution would be to add a function:Function TimeStamp(dummy As Double) As Date TimeStamp = Date + TimeEnd FunctionSet up a range of cells, dependant upon the cells driven by your DDE.Then use the inbuilt MIN function to tell which cell changed least recently. Have your spreadsheet sleep, then poll this time.If this is "too long ago" sleep again.As you've seen, polling is a bit more tricky in Excel if you want the app to remain responsive.For his you need OnTime Sub Poll() Application.OnTime Now + TimeValue("00:00:2"), "Poll"End SubThing to remember about OnTime is that it is a "one off" delayed procedure call, so you need tomake it "call" itself.You can also do neat/dirty tricks with OnTime like updating cells "from" functions.A more sophisticated approach is to use this function to validate the calculations by keeping track of which cells have been recalculated. This allows you to assert more complex notions such as at least "A1 or A2" must be recalculated to be valid.The price of this second style is that you must keep the code up to date.

Hi DCFC,The problem with the application.on time function is that it set a time for the subroutine to be excuted, but at the same time, it "branch out " to complete the rest of the macro code.Let me illustrate with my macro code.================================================for i=1 to number of filesopen file(i)'***have to wait for dde link to get updated*****run application ontime function to set time for calculation (eg 30 seconds)close file(i)next i=================================================The problem with the codes is that the macro actually went on to open all the files (without the dde update) and close them.Then after 30seconds, it open the files and do the calculation without the dde links update.

How about a self validating WorkBook ?This code will stop your sheet saviung unless/until the TimeStamp stuff I use in my last post is later than the opening time of the sheet.IE if there is a cell in this range, not recalculated since opening, the workbook will just try to save itself, until it is.I pick 5 seconds for the retry attempt, but your mileage will vary.This is not a "busy wait", processing will occur whilst the sheet is waiting for the predicate to become true, and of courseyou can use more complex logic to decide when to close, for instance #VALUE!'s may need correcting.Bung this stuff in your "ThisWorkBook" ModuleDim Opened As DatePrivate Sub Workbook_Open()Opened = Date + TimeMsgBox "opening"End SubPrivate Sub Workbook_BeforeClose(Cancel As Boolean)Dim LastUpdate As DateIf Opened = 0 Then Exit SubLastUpdate = Names("MinTime").RefersToRange.Value'If has not changed since openingIf LastUpdate < Opened Then Cancel = True Application.OnTime Now + TimeValue("00:00:5"), "TryToClose"Else Debug.Print "really closing" Opened = 0End IfEnd Sub'Drop this in a modulePrivate Sub TryToClose()Debug.Print "Closing"Workbooks(1).Close Application.OnTime Now + TimeValue("00:00:5"), "TryToClose"End Su

Hi all, I have found the solution to my problem.Thanks to DCFC and pb273 for their help.The solution is to combine DCFC and pb273 methods by having a while loop and keep track of the time you want to pause the macro. But the key point is that you need to add "doevents" inside the while loop to force excel to update the dde links while the marco pause.I have found the solution from this website from this websiteclick here