Asked by Steve Hall
on 20 Aug 2018

I am working with some code I inherited from my predecessor, and I am experiencing an unusual problem. Here is the basic algorithm (please don't ask me WHY):

- MATLAB code starts with some initial values in the MATLAB workspace
- These values get written to specific cells in the "inputs" tab in spreadsheet "inputs.xlsx"
- The MATLAB code then reopens "inputs.xlsx" and reads a larger set of values from the "input_master" tab in "inputs.xlsx" - by closing and reopening, the spreadsheet calculates values on the latter tab based on the new values on the former tab.
- The MATLAB code does a bunch of processing
- The MATLAB code writes some outputs to "outputs.xlsx"

Sometimes, in an as yet unpredictable manner, xlswrite simply overwrites the entire "inputs.xlsx" spreadsheet during step 2. It has come up with a prompt asking for permission to overwrite, but even when I deny it, it seems to happen anyway. It has only happened a few times so I am trying to run down what is making it do this. It does not appear to be directly repeatable - I can run the code twice with the same inputs and one time it will overwrite, the other time it won't.

This is problematic because the spreadsheet is quite elaborate, and more importantly, when it overwrites during step 2 it destroys what should be read during step 3.

I know that this is vague, and unfortunately the code is spread across a couple of dozen different m files so posting the whole code is right out. But does anyone have any ideas what would do this?

Answer by Image Analyst
on 20 Aug 2018

A couple of inconsistencies we need to clear up. You say "xlswrite simply overwrites the entire "inputs.xlsx" spreadsheet during step 2." However inputs.xlsx is not a spreadsheet, it's a workbook, a workbook with two sheets "inputs" and "inputs_master". So what gets overwritten? The whole workbook, both sheets? If so, what do you have after that -- both sheets or just one sheet?

Also you say " the "inputs" tab in spreadsheet" while the code refers to a sheet named "sim inputs". So which is it?

I suggest you set a breakpoint at the xlswrite() call and see what's being written. My guess is that either the size and shape of variable 1, 2, 3, or 4 is not what you think it is (for example a matrix rather than a row or column vector), or that the sheet name, "inputsheet", somehow got changed.

One way to prevent that is to compute the full rectangular range of the size you expect to write, for example 'A1:A99' or 'B4:H379' or whatever and make that the cell reference. Then if the variable size is not a perfect match for the cell range it will throw and error and you can investigate why. Currently when you only give one cell, like 'B1' it will put B1 as the upper left corner of whatever shape it is which will blast over other cells possibly. For example if variable 1 was a 5 row by 6 column matrix instead of a 6 column row vector, it will overwrite the entire block 'B1:G5' instead of just row 1 'B1:G1', thus obliterating anything you had in rows 2 through 4.

Steve Hall
on 20 Aug 2018

To clarify: wherever I said "spreadsheet" read that as "workbook" - a bad habit shared by many in my workplace.

The sheet is titled "sim inputs", I abbreviated in my earlier description on the assumption that the actual sheet name was not relevant - I have used xlswrite outside this particular code without issue using the full name.

When it overwrites, it overwrites all sheets in the workbook. Before running the code, there are more than a dozen named sheets. After running the code, when this overwrite randomly* occurs, the workbook has only the inputs sheet plus the default "sheet1", "sheet2", and "sheet3". The inputs sheet has only the values written to it in the previously shown code.

I can bound the individual xlswrite cell ranges to "B1:B1", but the sheets they are reading and writing from are heavily populated beyond what MATLAB is accessing. I cannot, for example, let them write a vector instead of a single value as that will overwrite cells that are referenced elsewhere by other code.

*: This code is running Monte Carlo simulations, so there are some truly random elements to the calculations!

Image Analyst
on 20 Aug 2018

Because the new sheet has the default 3 sheets (which you can put down to one in the preferences of Excel if you want), it seems like the workbook is totally gone, like something deleted it. Can you find it in the recycle bin? If it's MATLAB that's deleting it, it may just delete it outright unless you've issued the command

recycle on

before you start. dpb and I both suggested you figure out the expected cell range and write that explicitly rather than just giving the upper left cell. You can do this but you might want ot look up functions such as excel2col in the File Exchange to figure out the column letter from the number. For example it will give you AA for column 27

[lastRow, lastColumn] = size(yourData);

lastColumnLetter = char(ExcelCol(lastColumn));

cellReference = sprintf('A1:%s%d', lastColumnLetter, lastRow);

xlswrite(FileName, yourData, sheetName, cellReference);

Alternatively try writetable(0 like Jeremy suggested.

Sign in to comment.

Answer by Jeremy Hughes
on 20 Aug 2018

I suggest trying in a newer version of MATLAB. If it works there, contact technical support to report the bug.

I'd also suggest using readtable/writetable as I doubt they will have the same issue.

Sign in to comment.

Opportunities for recent engineering grads.

Apply Today
## 10 Comments

## Paolo (view profile)

## Direct link to this comment

https://au.mathworks.com/matlabcentral/answers/415542-xlswrite-randomly-overwrites-entire-spreadsheet#comment_601629

## Steve Hall (view profile)

## Direct link to this comment

https://au.mathworks.com/matlabcentral/answers/415542-xlswrite-randomly-overwrites-entire-spreadsheet#comment_601634

## Jeremy Hughes (view profile)

## Direct link to this comment

https://au.mathworks.com/matlabcentral/answers/415542-xlswrite-randomly-overwrites-entire-spreadsheet#comment_601637

## Steve Hall (view profile)

## Direct link to this comment

https://au.mathworks.com/matlabcentral/answers/415542-xlswrite-randomly-overwrites-entire-spreadsheet#comment_601640

## Adam Danz (view profile)

## Direct link to this comment

https://au.mathworks.com/matlabcentral/answers/415542-xlswrite-randomly-overwrites-entire-spreadsheet#comment_601643

## dpb (view profile)

## Direct link to this comment

https://au.mathworks.com/matlabcentral/answers/415542-xlswrite-randomly-overwrites-entire-spreadsheet#comment_601650

## Steve Hall (view profile)

## Direct link to this comment

https://au.mathworks.com/matlabcentral/answers/415542-xlswrite-randomly-overwrites-entire-spreadsheet#comment_601654

## Steve Hall (view profile)

## Direct link to this comment

https://au.mathworks.com/matlabcentral/answers/415542-xlswrite-randomly-overwrites-entire-spreadsheet#comment_601659

## Adam Danz (view profile)

## Direct link to this comment

https://au.mathworks.com/matlabcentral/answers/415542-xlswrite-randomly-overwrites-entire-spreadsheet#comment_601671

## dpb (view profile)

## Direct link to this comment

https://au.mathworks.com/matlabcentral/answers/415542-xlswrite-randomly-overwrites-entire-spreadsheet#comment_601704

Sign in to comment.