Pause RTD server in Excel and save worksheet

Question

I have worksheet which gets data from RTD server through the following formula:

=RTD("tos.rtd", , "ASK", ".SPX150220C750")

I would like to save the worksheet with above formula every 1 minute or so. The challenge is to pause VBA code and also make sure that before we save, the value in the cell is updated. I have tried the following code.

Sub Archiving()
For i = 0 To 4

    Worksheets("Test").Activate
    Application.Sheets("Test").Copy
    Application.DisplayAlerts = False
    ActiveWorkbook.SaveAs Filename:="D:\Save " & i & ".csv", FileFormat:=xlCSV
    ActiveWorkbook.Save
    ActiveWindow.Close
    Windows("Real time data.xlsm").Activate
    Application.DisplayAlerts = True

    Application.Wait (Now + TimeValue("0:00:05"))

    ActiveWorkbook.RefreshAll
    DoEvents
Next i

End Sub

The code does not work, simply because DoEvents waits until RTD is done with updates, which is never. I also have seen example where connection to DB is paused explicitly, but I don't know how to adapt it RTD server case. I tried to run RTD server from C#, but failed miserably. RTD in C# for dummies Any suggestions?

Answer 1

I had a similar issue. I added the following command in my VBA to trigger a RTD data refresh. I did this command before I used the data in my VBA macro. Hope this helps.

Excel.Application.RTD.RefreshData

Answer 2

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.