Opening an excel file, writing to it and closing it does not

% Name existing Excel file
ExcelOut='c:\UNSW\RESEARCH_ACTIVE\BP_Research\BP_UP_DOWN_INVASIVE\CRH_Data_NEW_10H.xlsx';
excelWorkbook=Excel.workbooks.Open(ExcelOut) % Open Excel 
RANGE=RowCol(in+4,23);  % Subroutine to define start of where to begin writing
writecell(A,ExcelOut,'Sheet',1,'Range',RANGE,'UseExcel',true,'WriteMode','inplace'); % Write to file
Excel.ActiveWorkbook.Save(); % Save Excel File
excelWorkbook.Close(false); % Close Excel File 
Excel.Quit; % Quit Excel file. Release resource

Get multiple error messages in MATLAB R2020b

ie

Unable to resolve the name Excel.ActiveWorkbook.Save.

Answer 1

[Walter Roberson]

excelWorkbook=Excel.workbooks.Open(ExcelOut) % Open Excel 

Excel.ActiveWorkbook.Save(); % Save Excel File

The names must match. You need

excelWorkbook.ActiveWorkBook.Save();

However, as @dpb noted, writecell() operates independently of activex operations.

[Branko Celler]

Thank you for clarifying that I do not need to use activex operations. However I am doing exactly what dpb suggested and while I successfully write the processed data to the first output file, nothing is written to the other four output files although the processing within the loop takes place correctly. I will try to provide the code to show exactly what I am doing

% Name excel file from which I read names of data files I am processing

fileExcel='c:\UNSW\RESEARCH_ACTIVE\BP_Research\BP_UP_DOWN_INVASIVE\CRH_Data_NEW_0H.xlsx'; 
% Process first part of that data thyat is common to all output files
% Create cell array of 118 variables, the first 75 of which are common to all output file
A=cell(1,118)
cell(size(A))
% write data in cells A(1,1) to A(1,75)
% start loop  for H= 10, 15, 20, 25, 30
for H=10:5:30
    % Process data
    % Write data to cell array A(1,76) to A(1,118)
    % set up filename for output file (Clumsy but effective!)
    if H==10
        ExcelOut='c:\UNSW\RESEARCH_ACTIVE\BP_Research\BP_UP_DOWN_INVASIVE\CRH_Data_NEW_10H.xlsx';
    elseif H==15
        ExcelOut='c:\UNSW\RESEARCH_ACTIVE\BP_Research\BP_UP_DOWN_INVASIVE\CRH_Data_NEW_15H.xlsx';
        
    elseif H==20
        ExcelOut='c:\UNSW\RESEARCH_ACTIVE\BP_Research\BP_UP_DOWN_INVASIVE\CRH_Data_NEW_20H.xlsx';
        
    elseif H==25
        ExcelOut='c:\UNSW\RESEARCH_ACTIVE\BP_Research\BP_UP_DOWN_INVASIVE\CRH_Data_NEW_25H.xlsx';
        
    elseif H==30
        ExcelOut='c:\UNSW\RESEARCH_ACTIVE\BP_Research\BP_UP_DOWN_INVASIVE\CRH_Data_NEW_30H.xlsx';
    end
    % Set up starting point in ExcelOut  from which to start writing 118 A cells
    RANGE=RowCol(in+4,23);    % my own subroutine to make it easier to identify                          Excel range
    % write data to ExcelOut
    writecell(A,ExcelOut,'Sheet',1,'Range',RANGE,'UseExcel',true,'WriteMode','inplace');
end

This works perfectly in writing to first ExcelOut file and processes data correctly for remaining four cycles of for loop, BUT does not write data to remaining four Excelout files. I dont know why, but suspect that I need to close each file after I write to it.

Would appreciate your help!

[dpb]

The above looks ok, but we can't debug your exact case without the data and seeing just what actually is happening.

As noted before, the write-XXX family of functions deal with all the opeining/closing internally, so that isn't the problem.

You need to set a breakpoint at the output line and inspect what is the content of A and show us a representative sample. writecell won't handle nested cell arrays, for example, but you're not showing us what the construction/content of A actually is.

While not the problem (probably), you coud write

outFolder='C:\UNSW\RESEARCH_ACTIVE\BP_Research\BP_UP_DOWN_INVASIVE';
for for H=10:5:30
  ExcelOut=fullfile(outFolder,sprintf('CRH_Data_NEW_%dH.xlsx'));        
  ...

to generate the filenames.

We need a minimum working example of the complete code and data that has the issue to be able to unequivocally debug it.

[Branko Celler]

Thank you for perservering!

Thank you also for the hint on how to improve the fine naming within the loop. I knew that my method was crude!

However wrt to the major problem I thought that a command

% Clear part of Cell Array that contains OWE data

A(1,76:118)={[]};

where I clear the contents of A(1,76) to A(1,118) at the end of each pass of the loop after I write the data to the excel file could possibly have cleared the whole array.

However commenting out that code had no effect, and I note that I can print out the whole cell array A at each pass and the content is correct, so producing the cell array is not the problem.

I have also made sure that I am NOT writing the data out to the same first file, but that is also NOT the case. The first output file contains the correct data, the subsequent four output files contain NO data. Nothing has been written to them.

So it would appear to me that writing sequentially to five different ExcelOut files without closing off each one after I write data to it should be the problem!

What do you think?

Regards

Branko

[dpb]

I think the problem is still what we can't see nor debug because we can't duplicate your exact situation.

And, NO, as stated before, writecell and friends handle all the file opening/closing internally each time they are called.

for i=1:4
  C={pi+i};
  fn=sprintf('Sample%d.xlsx',i);
  writecell(C,fn)
end
dir *.xlsx

Sample1.xlsx  Sample2.xlsx  Sample3.xlsx  Sample4.xlsx  

clear C
for i=1:4
  fn=sprintf('Sample%d.xlsx',i);
  readcell(fn)
end

ans = 1×1 cell array
    {[4.1416]}

ans = 1×1 cell array
    {[5.1416]}

ans = 1×1 cell array
    {[6.1416]}

ans = 1×1 cell array
    {[7.1416]}

delete *.xlsx

Unequivocally proves that the problem is NOT read/writecell being called sequentially.

[dpb]

You don't still have some old ActiveX processes hanging around from having crashed Excel when you were trying to use it, by any chance?

Close all open Excel workbooks and then open the Task Manager and kill all Excel processes that may still be hanging around; then try again.

[Branko Celler]

Hi dpb,

your last comment prompted me to write the command

system('taskkill /F /IM EXCEL.EXE')

at the very start of my program of 1691 lines of code to kill any EXCEL processes and it worked!

my FOR loop is now writing correctly to the five different excel files.

THank you so much for your help!

[dpb]

" to write the command system('taskkill /F /IM EXCEL.EXE') at the very start of my program"

I would strongly recommend to NOT leave that permanently; once you killed the zombie processes that had the other files locked, there's no need and it will add to the overall overhead to have to restart Excel.

Answer 2

[dpb]

You're mixing high-level MATLAB sritecell with code for direct ActiveX/COM interaction with Excel.

You don't need ActiveX here, anyway, writecell and the others (-table, -matrix, ...) handle all the opening and closing of the Excel file internally. Just

% Name existing Excel file
ExcelOut='c:\UNSW\RESEARCH_ACTIVE\BP_Research\BP_UP_DOWN_INVASIVE\CRH_Data_NEW_10H.xlsx';
RANGE=RowCol(in+4,23);  % Subroutine to define start of where to begin writing
writecell(A,ExcelOut,'Sheet',1,'Range',RANGE,'UseExcel',true,'WriteMode','inplace'); % Write to file

is all you need.

Also take out the other ActiveX code not shown unless you are going to do things such as formatting the sheet in ways that aren't supported by the builtin read/write functions. If that is the intent, then wait until after have written the data and then open the file with ActiveX or forego using writecell and do it all with ActiveX.

Answer 3

[Image Analyst]

See attached Excel Demo using ActiveX.