Is it possible to make matlab wait for a live user input on excel (opened with ActiveX)?
3 次查看(过去 30 天)
显示 更早的评论
Is it possible to make matlab wait for a live user input on excel (opened with ActiveX)? For example, assume the excel sheet is initially empty. If the user enters something in cell A1, matlab calculates something based on that value and punches it in in cell B1. Then the user enters something in A2, matlab calculates in B2, and so on.
Below is part of the code I wrote.
--------------------------------------------------
startTime = tic;
elapsedTime = toc(startTime);
i = 1;
while i < 5
% READ VALUES
sheet = 'Sheet1';
inputRange = ['A' num2str(i)];
while elapsedTime < 100
[data, text, rawData] = xlsread1(fileName,sheet,inputRange);
if ~isempty(data)
disp('read value');
break; % Exit from the loop since they entered something.
end
elapsedTime = toc(startTime);
end
%WRITE VALUES
myCalc = data^2; %CALCULATIONS YOU WANT
outputRange = ['B' num2str(i)];
xlswrite1(fileName, myCalc, sheet, outputRange);
i = i+1;
end
--------------------------------------------------
While matlab is waiting for a value in the cell A1, when I (i.e. the user) try to interact with the excel file and punch in a number in A1, it gives me the following error.
Error: Object returned error code: 0x800AC472
Error in xlsread1 (line 246)
Excel.DisplayAlerts = 0;
Error in FactSage_Matlab_Macro (line 69)
[data, text, rawData] = xlsread1(fileName,sheet,inputRange);
I'm using "xlsread1" and "xlswrite1" function from the links below.
www.mathworks.com/matlabcentral/fileexchange/22365-function-for-faster-data-transfer-matlab-%3C-%3E-excel
www.mathworks.com/matlabcentral/fileexchange/10465-xlswrite1
采纳的回答
Guillaume
2016-11-17
While you could indeed poll excel through activex and see if a cell has changed, I don't think you can do it with xlsread (or a variant of it) since it requires that the excel file of interest be closed.
In any case, polling is a waste of CPU when excel has events you can listen to and wait for:
excel = actxserver('Excel.Application'); %start excel
excel.Visible = true; %make excel visible so user can interact
workbook = excel.Workbooks.Add; %create a new workbook
worksheet = workbook.Worksheets.Item(1); %get 1st worksheet
registerevent(worksheet, {'Change', @(~, ~, range, ~, ~) fprintf('Range %s was changed\n', range.Address)});
%each time a cell is edited the event is fired
Documentation for excel change event
Thoroughly lacking matlab documentation for handling COM events
更多回答(1 个)
Walter Roberson
2016-11-17
No, it is not possible.
In Excel you could create a macro with MsgBox or InputBox to pop up a box that waited for input and then write that input into a cell and then use a MATLAB Engine call to trigger some action. However, there is no Excel operation corresponding to "wait for something to be input at such-and-such a location".
The closest you can get is to poll the location checking to see if something is there. Which is the approach Image Analyst described to you in your previous question http://www.mathworks.com/matlabcentral/answers/312220-how-to-make-matlab-wait-for-a-value-to-be-input-in-an-empty-excel-cell
There is a possibility that you might need to close down the Excel connection to the spreadsheet after every poll; I do not know. Also I do not know if you would need to "save" the spreadsheet before the contents were visible externally.
I see a suggestion that the particular error you are getting might be associated with attempting to access a cell while an excel dialog is popped up.
3 个评论
另请参阅
类别
在 Help Center 和 File Exchange 中查找有关 Spreadsheets 的更多信息
Community Treasure Hunt
Find the treasures in MATLAB Central and discover how the community can help you!
Start Hunting!