The challenge is to pause VBA code and also make sure that before we save, the value in the cell is updated.
THE PROBLEM with your previous implementation is that by doing it inside a loop, since VBA doesn't support multi-threading, the application was "busy" and unable to receive new data from RTD server.
This is based mostly on what I've gathered from Microsoft's documentation/knowledge-base, emphasis added:
The RTD function retrieves data from an RTD server for use in the
workbook. The function result is updated whenever new data becomes
available from the server and the workbook can accept it. The server
waits until Excel is idle before updating. This relieves the developer
of having to determine whether Excel is available to accept updates.
The RTD function differs from other functions in this regard because
other functions are updated only when the worksheet is recalculated.
And further suggests that toggling the application's .CalculationState etc. will have no effect on the RTD server:
Because RTD updates data when Excel is idle, it continues to receive
information if Excel is in manual calculation mode. In that event, the
new data is cached and the current values are used when a manual
calculation is performed.
So the data will be updated when it becomes available from the server (presumably not a problem) but what is a problem in your implementation is that the workbook can't accept it because it's running the VBA thread and an RTD formula is not a "normal" external link.
Although the RTD function provides a link to data on a server, it is
not the same type of link as references to cells in other worksheets
or workbooks. For example, if you use the RTD function in a workbook,
you do not receive the Links startup message when you open the
workbook, nor can you manage the status of an RTD function through the
Edit Links dialog box.
I suspect that another dissimilarity is that the RefreshAll method has no effect on this function, you can't force it to get external data because it's already doing so when the workbook can accept it.
POTENTIAL SOLUTION
By using the Application.OnTime event to schedule the save interval, I think you should be able to avoid the problem of the workbook being unable to receive data.
if you want to save the data at a regular interval, this function will call itself recursively, subject to the limitations of the Appliction.OnTime method:
Private Sub CreateArchive()
'Saves a copy of sheet "Test" and sets OnTime to save again in 60 seconds
Dim saveTime as String
saveTime = Format(Now(), "YYYY-MM-DD-hh-nn")
Worksheets("Test").Copy
Application.DisplayAlerts = False
ActiveWorkbook.SaveAs Filename:="D:\Save " & saveTime & ".csv", FileFormat:=xlCSV
ActiveWorkbook.Close
Windows("Real time data.xlsm").Activate
Application.DisplayAlerts = True
'Call on this function again in 60 seconds:
Application.OnTime Now + TimeValue("00:00:60"), CreateArchive
End Sub
NOTE: I can't replicate on my end because I don't have your COM object /etc. that is being called from the RTD function. So, take this with a grain of salt and understand that I am very limited in how much further assistance I can offer you.
