How to clear existing figure in excel using matlab?

I have an excel that is connected to matlab through active server. I am generating a graph via matlab and export it to excel. However, I need to clear the existing graphs first so it won't overwrite and make a stacked of graphs.

My idea is to find the existing graph first, and clear it. But, so far, I still don't know how to do it.

Anyone knows? I am open to any suggestions.

Thank you so much!

Elizabeth

[Guillaume]

It will be much easier to tell you how to delete the existing graph if you showed us how you created it in the first place.

[Elizabeth]

This is how I code it.

Excel = actxserver ('Excel.Application'); 
filename = 'xxx'
invoke(Excel.Workbooks,'Open',filename);
Sheets = Excel.ActiveWorkBook.Sheets;
Sheets.Item('Graphs').Activate;
ActiveSheets = Excel.Activesheet;
plotpie(cost,init_stage)
Shapes = ActiveSheets.Shapes;
ActiveSheets.Shapes.AddPicture('location', 0, 1, 400, 325, 345, 230)

So, I send input to the plotpie. The plot pie is basically pie chart with some modifications I make for the input. Then, I save the figure, and export it to excel.

function plotpie(cost,init_stage)
% some loop for the input
q = figure;
set(q, 'visible', 'off');
pie(cost,Process);
print('D:\location.jpg', '-djpeg')

[Walter Roberson]

Speculation:

Sheets.Item('Graphs').Delete;

[Guillaume]

I don't think Elizabeth wants to delete the graph actually. That would break her code. She wants to delete the shape that's been added to the worksheet.

Answer 1

[Guillaume]

Here is a more efficient version of your original code:

excel = actxserver('Excel.Application');
workbook = excel.Workbooks.Open(filename);
graph = Workbook.Sheets.Item('Graphs');
plotpie(cost, init_stage);
graph.Shapes.AddPicture('location', 0, 1, 400, 325, 345, 230);

To delete the picture you've added, and assuming that it is the only picture on the Graphs worksheet:

graph.Shapes.Item(1).Delete

[Elizabeth]

Hello, sorry for late reply. I will try it asap.

There are 4 graphs in the excel sheet. However, previous graphs needs to be removed before the new one is exported anyway so it won't stacked on each other. Thanks.

Answer 2

[Elizabeth]

The solution:

Sheets = Excel.ActiveWorkBook.Sheets;
Sheets.Item('Graphs').Activate;
ActiveSheets = Excel.Activesheet;

myshapes = ActiveSheets.Shapes;
for j = myshapes.Count:-1:2 
    myshapes.Item(j).Delete
end

thanks! Another reference: http://stackoverflow.com/questions/18958994/delete-excel-figures-from-matlab

[Guillaume]

Yes, if you want to delete all the shapes (note: they're not what excel call graphs, it's a completely different concept) you simply iterate over the Shapes collection elements and delete them one by one.

Note that it's a bit pointless to activate the 'Graph' Sheet just to get a reference to it. Your above code can be simplified to:

Sheets = Excel.ActiveWorkBook.Sheets;
GraphSheet = Sheets.Item('Graph');  

myshapes = GraphSheet.Shapes;
for j = myshapes.Count:-1:2 %why are you stopping at 2 and not 1?
    myshapes.Item(j).Delete
end

Answer 3

[Emanuel Arth]

Hi,

I have the same problem right now but I can't fix it with your code.

My working code (without the delete function):

sheet=Page %Indicates the Page on which I want to set the figure
Excel = actxserver ('Excel.Application'); 
File=Dateiname; 

if ~exist(File,'file') 
    ExcelWorkbook = Excel.workbooks.Add; 
    ExcelWorkbook.SaveAs(File,1); 
    ExcelWorkbook.Close(false); 
end 

invoke(Excel.Workbooks,'Open',File);
Sheets = Excel.ActiveWorkBook.Sheets;
SheetNo = get(Sheets, 'Item', sheet);
SheetNo.Activate;

   xls=Excel.ActiveSheet

   print (figure1,'-dbitmap'); xls.Range('I85').PasteSpecial; %Prints my figure on the page
      xls.Shapes.Item(1).Height=(2334/7);

invoke(Excel.ActiveWorkbook,'Save')
Excel.Quit 
Excel.delete 
clear Excel

I run this code 4 times. So I have 4 figures on my active Excel sheet. But when I run this code again I don't want to add new figures to the already existing - I want to delete the figures that are in this actual sheet and then add the new ones.

Can you help me? Which code do I need ? I tried following but it didn't work for me:

....

    SheetNo.Activate;

    Sheets.Item('Graphs').Activate;
    ActiveSheets = Excel.Activesheet;

    myshapes = ActiveSheets.Shapes;
    for j = myshapes.Count:-1:1 
        myshapes.Item(j).Delete
    end

    xls=Excel.ActiveSheet
  ...

Thank you !

Emanuel