Writing an excel sheet through MATLAB, then adding that sheet to an exisiting excel file (microsoft office 97-03 (.xls))

I have tried many, many different things. For further context, the excel workbook I am trying to add to contains macros and it has several sheets on it already. The closest I feel I have came to doing so was this code I found on a redditt forum:

excel = actxserver('Excel.Application');
workbook = excel.Workbooks.Open('C:\Users\user\OneDrive - User\Verification\Results.xls\',0,false);
sheet = workbook.Sheets.Item(4);
sheet.Activate();
range = sheet.Range('D1:D4');
range.Value = {'cool','this','actually','works'};
workbook.Save();
workbook.Close();

But this results in an error:

Error using Interface.000208DB_0000_0000_C000_000000000046/Open

Invoke Error, Dispatch Exception:

Source: Microsoft Excel

Description: Sorry, we couldn't find C:\Users\user\OneDrive - User\Verification\Results.xls\. Is it possible it was moved, renamed or deleted?

Help File: xlmain11.chm

Help Context ID: 0

[Walter Roberson]

workbook = excel.Workbooks.Open('C:\Users\user\OneDrive - User\Verification\Results.xls\',0,false);

I doubt that there should be the final \ in the Open.

workbook = excel.Workbooks.Open('C:\Users\user\OneDrive - User\Verification\Results.xls',0,false);

[Kendell]

It doesn't make a difference, I have tried that already.

Answer 1

[Voss]

Have you tried this?

C = {'cool','this','actually','works'};
filename = 'C:\Users\user\OneDrive - User\Verification\Results.xls';
sheet = 4;

writecell(C,filename,'Sheet',sheet)

[Kendell]

This does work and considering the simplicity of the example I provided I will accept this as an answer. However, I would need your variable "C" to be a large pre-exisiting xls file. Currently resulting to writing an xls file then using "readmatrix" to pull the sheet into matlab and outputting it using "writecell" onto my "Results.xls". If you have a better idea I am all ears!

[Voss]

"This does work"

Great! I'm glad to hear it.

"considering the simplicity of the example I provided I will accept this as an answer"

Thank you for (reluctantly) accepting my answer.

"I would need your variable "C" to be a large pre-exisiting xls file."

C is a cell array, not a file.

"Currently resulting to writing an xls file then using "readmatrix" to pull the sheet into matlab and outputting it using "writecell" onto my "Results.xls""

If you read the sheet using readmatrix, it seems like you would use writematrix to write it, not writecell, unless you do some processing which you haven't mentioned which makes the data into a cell array.

"If you have a better idea I am all ears!"

Maybe I would have an idea if I understood what the problem was. I gather you want to add a sheet to an existing xls file. Do I have that much correct? If so, note that you can use writecell/writematrix/writetable to add a sheet to an existing xls file (whether the existing file contains macros is immaterial). Example:

% existing file
filename = 'test.xls';
% the file contains one sheet
sn = sheetnames(filename)

sn = "Sheet1"

% which contains this stuff
readcell(filename)

ans = 1×4 cell array
    {'cool'}    {'this'}    {'actually'}    {'works'}

% now make a new sheet in the file
writecell({'here' 'is'; 'some' 'new';'stuff' '!'},filename,'Sheet',2)

Warning: Added specified worksheet.

% now the file contains two sheets
sn = sheetnames(filename)

sn = 2×1 string array
    "Sheet1"
    "Sheet2"

% the first sheet still contains this stuff
readcell(filename,'Sheet',1)

ans = 1×4 cell array
    {'cool'}    {'this'}    {'actually'}    {'works'}

% and the second sheet contains the new stuff
readcell(filename,'Sheet',2)

ans = 3×2 cell array
    {'here' }    {'is' }
    {'some' }    {'new'}
    {'stuff'}    {'!'  }

If I am missing some aspect of the task or the challenge you are facing, please clarify. In particular, does using writecell to create a new sheet in an existing xls file answer your question satisfactorily or not? If not, what's the problem?

[Kendell]

Thank you for the help this worked!.

I gather you want to add a sheet to an existing xls file. Do I have that much correct? If so, note that you can use writecell/writematrix/writetable to add a sheet to an existing xls file (whether the existing file contains macros is immaterial).

You did have that much correct. Sorry for the lack of clarity, but readtable & writetable worked as there was a lot of data to be appened to the exisiting xls file. Just did separate writetable functions, one for numeric values and one for variable names.