暂停Excel中的RTD服务器并保存工作表 [英] Pause RTD server in Excel and save worksheet

问题描述

我有一个工作表，它通过以下公式从RTD服务器获取数据:

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

我想每隔1分钟左右用上述公式保存一次工作表.面临的挑战是暂停VBA代码，并确保在保存之前，更新单元格中的值.我已经尝试了以下代码.

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

该代码不起作用，仅仅是因为DoEvents等待直到RTD完成更新为止，这是从来没有的.我还看到了显式暂停与数据库的连接的示例，但我不知道如何适应RTD服务器的情况.我试图从C#运行RTD服务器，但失败了. 在C#中用于虚拟人的RTD 有什么建议吗?

解决方案

面临的挑战是暂停VBA代码，并确保在保存之前，单元格中的值已更新.

问题与您以前的实现有关的问题是，通过在循环内执行此操作，由于VBA不支持多线程，因此应用程序繁忙"并且无法从RTD服务器接收新数据.

这主要基于我从Microsoft的文档/基于知识库的内容中收集的信息，重点已添加:

RTD函数从RTD服务器检索数据，以用于 工作簿.每当有新数据出现时，函数结果就会更新 可以从服务器上获取，并且工作簿可以接受.服务器 等到Excel空闲后再进行更新.这减轻了开发人员的负担 确定Excel是否可用于接受更新的说明. RTD功能在这方面与其他功能有所不同，因为 其他功能仅在重新计算工作表时才会更新.

并进一步建议，切换应用程序的.CalculationState等将不会对RTD服务器产生影响:

因为RTD在Excel空闲时更新数据，所以它继续接收 有关Excel是否处于手动计算模式的信息.在这种情况下， 缓存新数据并在使用手册时使用当前值 计算.

因此，当服务器上的数据可用时，数据将被更新(大概不是问题)，但是实现中的问题是工作簿无法接受它，因为它正在运行VBA线程，而RTD公式是不是正常"的外部链接.

尽管RTD功能提供了到服务器上数据的链接，但它是 链接类型与其他工作表中对单元格的引用不同 或工作簿.例如，如果您在工作簿中使用RTD功能， 当您打开 工作簿，也无法通过以下方式管理RTD功能的状态: 编辑链接对话框.

我怀疑另一个差异是RefreshAll方法对此功能没有影响，您不能强迫它获取外部数据，因为它已经这样做了当工作簿可以接受它时

潜在的解决方案

通过使用Application.OnTime事件安排保存间隔，我认为您应该能够避免工作簿无法接收数据的问题.

如果您要定期保存数据，则该函数将递归调用自身，但要受

注意:我无法复制，因为我没有您的COM对象/etc.这是从RTD函数调用的.因此，带着一粒盐，并明白我能为您提供多少进一步的帮助非常有限.

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?

解决方案

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.

这篇关于暂停Excel中的RTD服务器并保存工作表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！