fml
I'm trying to write a macro that takes a list of workbooks (each corresponds to financial data for a given ticker), opens the associated company workbook , waits for an external data source to update (financial data from bloomberg) then copies some updated cells to a parent spreadsheet and moves on to the next ticker.
My problem is that the data feeds are asynchronous so I have to stop and wait for the feeds to update before closing and moving on to the next woorkbook. The code below attempts to do that.
Code:
Public Sub RefreshStaticLinks()
Application.Run "RefreshData" 'Trigger refresh of Bloomberg links
Call Application.OnTime(Now + TimeValue("00:00:01"), "ProcessData") 'Wait one second then call 'ProcessData' again
End Sub
Public Sub ProcessData()
For i = 0 To 2
If Cells(11 + i, 1).Value = "#N/A" Then 'If data has not returned yet
Call Application.OnTime(Now + TimeValue("00:00:01"), "ProcessData") 'Wait one second then call 'ProcessData' again
Exit Sub
End If
Next i
End Sub
The problem is after the application.ontime function gets going the macro doesn’t actually stop. It closes the company specific workbook and opens the next long before the data is actually updated. I know there has to be a way around this but I’m lost.