Extracting RTD (Real Time Data) from Excel file

Question

I have an Excel 2010 file containing auto-updating cells with RTD. Example of cell:

=RTD("xxx";"yyy")

I need to extract (in a .txt file) those cells' values, every X minutes. My .txt file MUST contain the updated value.

I tried with a macro. That macro exports every X minutes a txt file of the Excel file. The problem is that when macro is running, cells doesn't update: the values remain the same of those before the macro was launched. It looks like macro forbids the updating.

How can I do?

Thank you.

Answer 1

First, go to Excel's File menu, and click on Options. Under Formulas > Calculation Options > Workbook Calculation, select Manual. Now you can add a simple command to your VBA code to update your cells. This command is Application.Calculate (or just Calculate). Hope that helps.

Answer 2

From your question i guess you have some sort of timing loop in your macro that waits for the delay time before doing the logging, but the macro continues to run.

If this is so then a better method is to use Application.OnTime which allow scheduling a macro to run at a specific time. in the interim control is returned to excel normally

To get you started here's an outline

Sub YourSub()
    <do your logging stuff>

    if not <terminal condition> then
        Application.OnTime Now + TimeValue("XX:XX:XX"), "YourSub" 
    end if
end sub