how to paste figure to excel file and then save and exit excel?

I generated an excel file in my MatLab script, added data to it, and then using PlotInExcel (below), I have added a figure:

function PlotInExcel
   x= {1:10};
   a= cell2mat(x);
   y= {1:10};
   b= cell2mat(y);
   %............plotting..............................................................................................
   plot(a,b);
   xlabel('X Values');
   ylabel('Y Values');
   print -dmeta;   %.................Copying to clipboard

   FILE  = 'C:DATA.xlsx';
   Range='OL14';
   %.............excel COM object............................................................................
         Excel = actxserver ('Excel.Application');
 Excel.Visible = 1;

 if ~exist(FILE,'file')
       ExcelWorkbook=Excel.Workbooks.Add;
       ExcelWorkbook.SaveAs(FILE);
       ExcelWorkbook.Close(false);
 end
 invoke(Excel.Workbooks,'Open',FILE); %Open the file
 ActiveSheet  = Excel.ActiveSheet;
 ActiveSheetRange  = get(ActiveSheet,'Range',Range);
 ActiveSheetRange.Select;
 ActiveSheetRange.PasteSpecial; %.................Pasting the figure to the selected location
 
%-----------------------------------end of function"PlotInExcel--------------------------------------

Now I want to add a second figure to another Range (so it doesn't overwrite the first figure). But if I don't manually Save and Exit the Excel file before re-running PlotInExcel (with a new Range, of course), PlotInExcel merely erases the first figure and adds the second one. Yet, if I manually save/exit my Excel file, PlotInExcel with the new range works fine.

How can I either (1) edit the above PlotInExcel functon to Save/Exit my excel file after adding a figure, or (2) edit my script to Save/Exit the excel file after running PlotInExcel?

I should add that the following, which worked well before I included PlotInExcel, worked fine to save my file:

invoke(Workbook,'Save')                                                     % Save file
invoke(Excel,'Quit');                                                       % Close Excel and clean up
delete(Excel);
clear Excel;

..but no longer does the trick.

Answer 1

[Stephen]

With enough bashing around, I was finally able to answer my own question. I'm posting the resolution here in the event anyone else has this problem. I removed the lines at the beginning because they just produced a test figure. The function below assumes you have your own figure you want to paste into an excel spreadsheet. BTW, you must have your figure saved to the clipboard before running this function.

function PlotInExcel
   
   FILE  = 'C:\DATA.xlsx';     %identify the name and location of your excel spreadsheet
   Range='R1';                 %select where you want your figure pasted in the speadsheet (cell ID)
   %.............excel COM object............................................................................
         Excel = actxserver ('Excel.Application');
 Excel.Visible = 1;

 if ~exist(FILE,'file')
       ExcelWorkbook=Excel.Workbooks.Add;
       ExcelWorkbook.SaveAs(FILE);
       ExcelWorkbook.Close(false);
 end
 invoke(Excel.Workbooks,'Open',FILE); %Open the file
 ActiveSheet  = Excel.ActiveSheet;
 ActiveSheetRange  = get(ActiveSheet,'Range',Range);
 ActiveSheetRange.Select;
 ActiveSheetRange.PasteSpecial; %.................Pasting the figure to the selected location
  Excel.ActiveWorkbook.Save     % Now save the workbook
     if eq(Excel.ActiveWorkbook.Saved,1)
         Excel.ActiveWorkbook.Close;
     else
         Excel.ActiveWorkbook.Save;
     end
     invoke(Excel, 'Quit');    % Quit Excel    
     delete(Excel);            % End process
end

[JClarcq]

Can we do that without using Activex such that it runs in webapp?

Answer 2

[Ayush Gupta]

The problem is arising because at the end of the function, once the graph is pasted to the excel file, it is not saved and directly exit from the program which is the main issue behind this. Refer to the following code for a work-around.

xls = xl.Workbooks.Open('myexcelfile.xlsx'); % Open specific document 


% Perform functions like attach graphs to it or do anything 

 
xls.Save; % Save the document 

xl.Quit; % Close the document 

xl.delete; % Remove Excel reference to allow the document to be opened independently 

Also to see some example on how to use to use actxserver with Excel use the following link.

[Stephen]

Thanks, Ayush! However, I'm sufficiently unfamiliar with Matlab-to-Excel that I can't see where your suggested code (or the suggestions on the link you kindly included) should be used. Specifically, the PlotInExcel function uses "invoke" to open the file:

invoke(Excel.Workbooks,'Open',FILE); %Open the file
 ActiveSheet  = Excel.ActiveSheet;
 ActiveSheetRange  = get(ActiveSheet,'Range',Range);
 ActiveSheetRange.Select;
 ActiveSheetRange.PasteSpecial; %.................Pasting the figure to the selected location
 

If I (naively) substitute

 xls = xl.Workbooks.Open('myexcelfile.xlsx'); % Open specific document 

for the "invoke" line to open the file, I get an error

Can you instruct me how to change the PlotInExcel fnx to get the sheet to save/close/delete so I can re-open to add another figure later in my script.

BTW, I am aware that there is a typo in the PlotINExcel I included needs a backslash:

 FILE  = 'C:\DATA.xlsx';