MatLab command to replace F9 button in Excel with rand() function

I have an excel file generating and elaborating random numbers starting with the rand() function and then applying some other operations. The final column of values changes every time that F9 is pressed. I'm trying to create a MatLab script that reads that column and saves it in a matrix, then "refreshes" the excel sheet with an equivalent command to F9, reads the new column and saves it in a new column in the matlab matrix. This procedure needs to be performed in loop for several times, but I cannot figure out how to refresh the randomly generated numbers in excel with a matlab command. Thanks in advance for your help!

Answer 1

[Rishi Binda]

I understand that you want the updated values of the Excel sheet column in your MATLAB Matrix without manually opening the sheet or pressing refresh (F9).

First you can set you workbook to automatically refresh data at regular intervals. Set the Connections Properties in the Data tab and select the Refresh every option. Set the refresh time.

Now you can create a server for Microsoft Excel in MATLAB by using actxserver. This creates an active session of the excel file without opening it. You can read, save and close the file. Here is the link for reference :

Using ActiveX

You can put the part of the code where it reads the data in a for loop and use pause function to set a wait time equal to your refresh time of the excel sheet. It will run the loop iteration after the sheet is updated and read the data of the updated sheet.

for i=1:100
    %code for reading data
    pause(%wait time)
end

Here are some links which are on the same thoughts if you require more insights :

https://www.mathworks.com/matlabcentral/answers/222796-matlab-and-microsoft-excel?s_tid=answers_rc1-3_p3_Topic

https://www.mathworks.com/matlabcentral/answers/93675-is-it-possible-to-call-excel-macro-functions-from-matlab

Hope this helps!

-Rishi