Read and write data to an open excel file.

Hello everyone, I need to ask how matlab R2017b can read data from an open excel file than write the data to the same file ? Because now it can't run without close the file.

Thank you !

Answer 1

[Emmanuel J Rodriguez]

Hello, take a look at this answer: https://www.mathworks.com/matlabcentral/answers/577870-write-to-an-already-opened-excel-file#answer_478051

[Saidi Chawki]

Thank you, i tried with these codes but it gives the same error. I don't know where the problem is.

I hope I will have the solution

[Emmanuel J Rodriguez]

Hello! Can you share the code that you are working on?

[Saidi Chawki]

Hello ! This code I need to read fromthe opened file and write.

[Emmanuel J Rodriguez]

Hello! Below is the code to read/write to an open MS Excel file:

%% Create Excel Automation Server
% Run the Excel application in an Automation server process
% using the 'actxserver' function and the program ID, excel.application
exl = actxserver('excel.application'); % Creates an Excel object

% Use the 'Workbooks' interface to open the Excel file containing the data
exlWkbk = exl.Workbooks;
exlFile = exlWkbk.Open(['C:\Users\ejoaq\OneDrive\1 My_Notebook\3 Engineering\Programming - Logic and Design\MATLAB\Read_and_Write_Data\testReadWrite.xlsx']);
exl.Visible = 1; % Display Excel file by setting the Visible property to 1

% Use the workbook 'Sheets' interface to access the data from a 'Range'
% object
exlSheet1 = exlFile.Sheets.Item('Sheet1');
robj = exlSheet1.Columns.End(4); % Range object, find the end of the column
% For robj to find the end of the column, data must start at row 1, col 1

numrows = robj.row; % And determine what row it is
dat_range = ['A1:F' num2str(numrows)]; % Read the last row

rngObj = exlSheet1.Range(dat_range); % Calls the exlSheet1 object and uses
% the 'Range' method to operate on the data range, 'dat_range'

% The entire data set from the Excel file's 'sheet1' is accessed via
% the range object interface 'rngObj'
exlData = rngObj.Value;

% Convert the numerica data to a double matrix.
exlDataNums = cell2mat(exlData(2,:));

%% Write Spreadsheet Data Using Excel as Automation Server
% Make the first sheet active
% exlSheets = exl.ActiveWorkbook.Sheets;
% exlSheet1 = exlSheets.get('Item',1);
% exlSheet1.Activate

% Assign variables to numeric data
N = exlDataNums(1)
x = exlDataNums(2)
R = exlDataNums(3)
C = exlDataNums(4)

% Logic
xNew = C - R;
A = nan;
if N < R
    A = 12
elseif N > C
    A = 8
else
    A = 10
end

% Put MATLAB data into the worksheet
exlActivesheetRange = get(exl.Activesheet,'Range','E2:E2'); % Get active sheet, assign range
exlActivesheetRange.Value = A; % Write data to (open) Excel file

% Read data back into MATLAB
%exlRange = get(exl.Activesheet,'Range','F1:F3');
exlRange = exlActivesheetRange;
A_readback = exlRange.Value; % Type = cell array

% Save the file
exlFile.Save

% Close Workbook
exlWkbk.Close

% Terminate Excel Automation Process
% Since Excel Automation server runs in a seperate process from MATLAB, you
% must terminate this process explicitly.
exl.Quit

exl.delete % Delete the server object

[Saidi Chawki]

Hello ! thank you for your help !

Why does it give the error with a .xlsx file?

What type of file should it be since the same error occurs with .xlsm?

Thank you.

[Emmanuel J Rodriguez]

The .xlsx file extension works fine on my end.

Ensure that the file path to the target spreadsheet is correct, in other words change the path provided in the example to your path...

exlFile = exlWkbk.Open(['MS_EXCEL_FILE_PATH_GOES_HERE']);

[Emmanuel J Rodriguez]

Also, make sure all MS Excel instances are closed before executing the code.

[Saidi Chawki]

Thank you for your help, I have tried it and it works well. There was a problem in my machine.

Answer 2

[John Bishop]

A simple workaround method that might work for some is to make a local temporary copy of the open file and work on that, then copy it back to the original when ready. It is also a safe method of working as it would not touch the original data until told to do so.

The 'system' command can run an OS command, e.g. on a MS Windows machine:

originalfilename='Book1.xlsx';
tempfilename='tmp.xlsx';
system(sprintf('xcopy %s %s /Y',originalfilename, tempfilename));

On MS Windows the 'xcopy' command with a /Y option copies the file and overwrites without prompting. Also xcopy is not blocked by the file being open elsewhere whereas a straight 'copy' is blocked. I'm sure there is a similar Linux command.