Copy an Excel worksheet from one workbook to another with Matlab

Hi everyone,

I hope you can help me with this problem. I need to copy a worksheet of workbook1 to another workbook2 with exact the same structure/format. There are empty cells and labels in between which I need to copy as well. I tried to save the range in a variable, but that doesn't work for me. I would like to do this via an ActiveX server. Is this possible and what would the code look like? Thanks in advance!

Greetings

Answer 1

[Guillaume]

You may as well write a macro in excel because you'd be using the same code in matlab:

excel = actxserver('Excel.Application');  %start excel
wbsource = workbooks.Open('C:\somefolder\somexcelfile.xlsx');
wbdest = workbooks.Open('C:\somefolder\someotherexcelfile.xlsx');
ws = wbsource.Worksheets.Item('NameOfSheet');  %or you can you the sheet index: ws = wbsource.Worksheets.Item(index);
ws.Copy(wdest.Worksheets.Item(1));  %copy worksheet before 1st worksheet of destination workbook
wdest.Save   %save destination workbook
excel.Quit  %quit excel

I've just written this on the fly. It's untested there may be bugs.Use MSDN to find the right functions to call.

[Franziska]

Thank you very much! This works perfectly! :)

Answer 2

[Pruthvi G]

https://in.mathworks.com/matlabcentral/fileexchange/72901-copy-sheet-from-one-excel-file-to-another-excel-file

Answer 3

[Neuropragmatist]

Why does readtable followed by writetable not work?:

https://uk.mathworks.com/help/matlab/ref/readtable.html?searchHighlight=readtable&s_tid=doc_srchtitle

https://uk.mathworks.com/help/matlab/ref/writetable.html?searchHighlight=writetable&s_tid=doc_srchtitle

If the spreadsheets dont have to be in the same file you could also use movefile to just duplicate the original:

https://uk.mathworks.com/help/matlab/ref/movefile.html?searchHighlight=movefile&s_tid=doc_srchtitle

Sorry I don't really know about activeX servers.

Hope this helps,

M.

[Image Analyst]

Does readtable retain formatting? Like font size, color, background fill, border sizes, etc.? I'd doubt it.

[Neuropragmatist]

No you are right it doesn't...