xlswrite Function 'openExcelWorkbook' Not Working! (New 05/2023)

Hello all,

I am having trouble with function xlswrite. I have used it countless times before with the same exact script earlier this year. Today, however, I am receiving an error message despite no changes to my MS Excel licensure/version or MATLAB licensure/version. I have not updated security protections either. Essentially, nothing has been intentionally changed on my end since last using xlswrite in January.

On MATLAB 2018b, I get the following error message:

Error using xlswrite (line 224)

Undefined function 'openExcelWorkbook' for input arguments of type 'COM.Excel_Application'.

On MATLAB 2021a, I get the following error message:

Unrecognized function or variable 'openExcelWorkbook'.

Error in xlswrite (line 224)

theMessage = exceptionHandler(nargout, exception);

I could not find documentation nor previous questions raised which involve these error messages. Any thoughts?

[dpb]

What does

which -all openExcelWorkbook

return? It's a private function called by xlswrite; the messages make it look like it isn't being found, but seems hard to imagine would have scrogged two versions -- unless, of course, you somehow have a piece of code that uses that name for something else...

[Michael Diaz]

C:\Program Files\MATLAB\R2021a\toolbox\matlab\iofun\private\openExcelWorkbook.m % Private to iofun

Answer 1

[Image Analyst]

Can you start Excel by itself without it asking you anything? What happened to me was that my systems were changed from using Excel 2016 to using Excel/Office 365. This ended up breaking all my programs because when I went to toss some data into Excel, it failed. For example, Office 365 requires everyone to login to Office at least once. So when my users went to a shared lab system and logged in under their own username to the system, and ran my program, my program called up Excel as an ActiveX server and then tried to throw data into it, and that failed. The cause was that now it was Office 365 that was running and my user was supposed to login to office. However, this login prompt happened immediately, even before I could set the Visibility property to true! So my user never saw the login prompt and my attempt to use Excel after that failed. With the prior version of Excel, we did not have to have each user login individually. The fix was that I have to have my users start Excel on it's own and login, once. Once that is done, then I can get an ActiveX server without it asking my users to login anymore. The problem is we have many users and they change lab technicians all the time. We are looking into getting something called something like a "long term service contract" where the Excel is for the whole machine, like it used to be, rather than for each individual users like it is now with Office 365. However they'll do that only until 2025 when Windows 10 and that version of office will have "end of life".

So bottom line, try to start Excel by itself first and answer any prompts it puts up. Then see if you can start it from MATLAB.

Another option is to change your call call from xlswrite to writecell or writematrix.

[dpb]

@Image Analyst, I've got stuff running on system at local community college that's on O-365 and been using the FEX modified xlswrite that maintains the ActiveX connection between writes and haven't had any such issues.

[Michael Diaz]

@Image Analyst @dpb That was my first thought -- I opened MS Excel and retried... same error codes. Then, I ran MS Excel as administrator... same error codes. For the time being, I have switched over to writecell and writematrix, but I'd like to figure out what happened to my beloved xlswrite.

[dpb]

Have you tried setting breakpoint inside xlswrite and traced what's happening?

I've got 2020b running both interactively and with compiled apps using the modified xlswrite that is just the same code from about R2018 or thereabouts copied over and edited to create the COM server externally first.

And, if that were the case of the user logging into Office, I don't see how that would be any less of an issue with writeXXX family; Office would still complain if that were the case, it seems to me.

I've run into issues with the changing inside O-365 to disable macros by default that have had to change settings to allow, but that didn't stop writing to Excel; just that the macros wouldn't run.

[dpb]

Just another random thought -- the college is an all-MS operation and everybody uses Outlook -- could it be it's having connected to it that has satisfied the log-in requirement, maybe, that would satisfy the issue @Image Analyst had? Whatever it is, it hasn't (yet, anyways) caused a problem in the Foundation office...

[Image Analyst]

@dpb yes, I think you just have to login to any office app once and then you're good to go. At least for a while. I find sometimes I still need to login to Microsoft but I think it's mostly when using the browser, not desktop apps.

@Michael Diaz try restoredefaultpath and then try again.

[dpb]

@Image Analyst, the other issue I've had with the switch to O365 and the increasing security MS has introduced inside Office is that creating/writing files from off the secure network causes workbooks to be opened in safe mode such that nothing can be done with them by the user. That required changing permissions to open up the access inside Excel to get around. The way the apps I've got work, I don't think I ever tried the xlswrite1 code on one of those after it was on the network before loosening up the settings so not sure if could have done or not. I was building on local machine and saving to network/college machine rather than running over the VPN because my bandwidth makes the latter painful to use Excel/MATLAB directly logged in on the college machine.

MS is making it harder and harder to stomach/get anything done, but there's no hope of the college moving away...this changeover vendors are making to cloud- and subscription-based apps is really, really bad direction imo as user; particularly one with limited bandwidth as we've talked about re: the install issues with MATLAB.

[dpb]

IA, I'm also curious re: your suggestion to use the writeXXX family instead -- do they actually behave differently in not requiring the login? That would be useful certainly, and interesting to know if they can bypass such or if they just somehow manage to display the login prompt instead of failing.

The big problem still is they also suffer from the problem that they open/close (and I presume create/destroy) the file/COM object on every call as does xlswrite. This is ok for moderately dense operations on a file, but I've found they become excessively slow and can even hang the machine if try to write very large numbers of cells in sequential calls when it is inconvenient/difficult to create all the data a priori and update/write the whole file (or at least significant fractions of it) in one operation.

These routines need a helper option that would let the user control when the actx server/file is opened/closed to be able to open the file, work on it a while with mulitple calls, then close when done.

[Image Analyst]

Yeah, Office 365 is not so reliable. In January, March and April 2023 it had problems. In fact, it had problems just yesterday:

https://www.bleepingcomputer.com/news/microsoft/microsoft-365-hit-by-new-outage-causing-connectivity-issues/

May 22, 2023: "We're investigating an issue affecting access to https://msft.it/6016gbJ06 and Microsoft 365 apps," the company said. Additional updates can be found at https://msft.it/6017gbJ0B under MO559201."