Writing to table in matlab does not appear in a shared workbook?

Trying to write to a excel workbook that has shared privileges. The shared workbook works when two seperate people open it and make changes. Where there is conflict a box appears that allows the users to accept the changes etc...

When i write on the same line using matlab, then use the write command to save it. The changes do not appear on the opened workbook when clicking save. It just saves whatever is in the current workbook at that time with 0 conflicts, even though matlab has already wrote to it.

You only see the changes when you close the workbook and open it again without hitting save.

Is there a way round this ?

[dpb]

Are you using '...,'UsingExcel',1 in the writetable call?

I'm not terribly surprised of there being issue if the file is already open--the internal Open operation is going to create a new instance; it won't access the already open instance you're looking at interactively.

I haven't researched the documentation about whether this might be doable in VBA via COM/ActiveX, but I suspect that would be the only way possible, not with the present toolset.

[MKM]

I havent tried UsingExcel,1, will give it a shot. What does this parameter do? I couldnt see it in the documentation.

I have attempted to create a visual basic app through excel itself but run into similar problems... That and MATLAB having more features/functions to use for my app.

Thanks for your help though!

[Stephen23]

The option is actually UseExcel:

https://www.mathworks.com/help/matlab/ref/writetable.html#mw_419f9564-b452-4d81-932d-30f750bc8943

[MKM]

Cheers Stephen. After reading the documentation, sounds like that might be what i need with the write command. Fingers crossed!!

[MKM]

This does work, however the matlab app im working on would need to write often to the excel spreadsheet. This solution would results in multiple windows being opened of the same workbook. Not ideal. Cheers anyway!

Answer 1

[Naga]

To manage real-time updates and conflicts in shared Excel workbooks when using MATLAB, use Excel's COM Automation server instead of simple file operations. This approach allows you to interact with Excel programmatically as if you were using it manually, reducing conflicts. Here's a concise example of using Excel's COM Automation server from MATLAB to manage shared workbooks:

% Example of using Excel COM Automation in MATLAB
excel = actxserver('Excel.Application');
workbook = excel.Workbooks.Open('path_to_your_shared_workbook.xlsx');
sheet = workbook.Sheets.Item('Sheet1');

% Make your changes
sheet.Range('A1').Value = 'New Value';

% Save the workbook
workbook.Save();

% Clean up
workbook.Close();
excel.Quit();
delete(excel);

[MKM]

Thanks Naga. Does this open an instance of Excel? If so, it wouldnt be ideal as i would be writing often to the spreadsheet!

[dpb]

Yes, but not the one somebody else already has open; it creates it's own instance.

Also, of course, you then have to write everything you do in COM parlance which, depending upon what you're actually wanting to do, be very simple or quite painful because you don't have access to the VBA compiler to write a lot of high-level expressions like "With" constructs; everything has to be parsed down to the actual object method/property.

I have only just begun trying to use a shared Excel document and am finding it very painful to do anything with programmatically; starting with since it requires saving to the cloud to do so, I've found no way from MATLAB to actually open that shared file link...and I've also discovered that if one saves a local copy that the writeXXX rountines quietly fail to update it, as well; I've not yet been able to unearth just what's going on there.

If you have a way that you can actually open the shared file directly from MATLAB, I'd be most interested in how you created the share and then how to access it. The only thing I've found that allows another app access to the shared file other than from the shared link manually interactively is via additional software APIs to do the verfication to get around the security, and those look to be pretty complex unless it's a serious project worthy of serious effort...

[MKM]

Im sure you dont actually have to save it to the cloud. There is a legacy option with regards to shared workbooks. You need to go through the Excel options > customize ribbons > Review > share and select the legacy option.

Not sure if that helps, but it might help you get around the saving to cloud part. This way requires only saving the workbook on the network.

[dpb]

OK, I hadn't found that...I'll go poke around and hopefully find it...I just followed the directions when tried to share the working. Thanks for the tip...

Look at the FEX for sample Excel COM code -- there's a class of utility functions out there that is pretty useful but they've mucked up the user interface so badly that I can no longer find it...and, unfortunately, I inadvertently lost the contributor info while munging on the copy I downloaded and no longer remember whose it is...but it is one of the frequent contributors here.

I just went through about a dozenof those I remember well and none of them registered a hit, though...unless it has been removed for some reason which I'd not think likely.

[dpb]

"... you dont actually have to save it to the cloud."

Excepting I realize in this particular case I do because I need to share it with some folks who are not on the network... :(