List of all opened Excel Workbooks in Matlab R2018b

Hello,

I am trying to create a list of all opened Excel files (some might be new and not yet saved) using

e = actxGetRunningServer('Excel.Application') 

as shown in some examples on this website, but

e.Workbooks.Count 

always returns 0 and does not see any open workbooks (I had 2 open when testing this).

I am using version R2018b on Windows 7, and the variable values are:

e: COM.Excel_Application

e.Workbooks.Count: 0

I also tried using

actxserver('Excel.Application')

but it does not help either. Can anyone please tell me what needs to be changed in new version of Matlab to make it work?

Answer 1

[Guillaume]

Unfortunately for you, this behaviour is not controlled by Matlab but by Excel and your OS. In particular, the behaviour may change depending on the version of Excel (not matlab). You'll have more problems with older versions of excel.

It would be interesting to know what:

e.MergeInstances

returns after you've connected to excel (with actxGetRunningServer) and how many excel processes you've got running (again after you've connected to excel).

[Roman N]

Thanks Guillaume -- your comments were helpful.

e.MergeInstances

returns logical 1. And there were 3 instances of excel running in task manager (maybe because of me using actxserver('Excel.Application')). After I closed them and opened Excel files, the code

e.Workbooks.Count

worked correctly, returning 2 (and I was able to list the workbooks by name), so there is definitely an interplay of System/Excel/Matlab. I think the issue was in Matlab connecting to the wrong Excel server/process while, the opened workbooks were assigned to a different one. Still not fully sure, but I can work with this. If you have a bit more insight, please share and I will close the question as answered. Maybe there is a way to loop over excel servers?

[Guillaume]

"I think the issue was in Matlab connecting to the wrong Excel server/process"

If you have several excel processes running, it's pot-luck which you connect to. As documented in actxgetrunningserver, "If multiple instances of the server are running, then the operating system controls the behavior of this function.". (and actually, I believe excel has a say in this).

Whether or not you get a new process when you use actxserver is also under control of the OS/excel. It's not something you can control from matlab. The underlying technology (Microsoft Component Object Model) gives no control to the client (matlab here) how the server is started. It's all down to the server.

[FunTom]

@Roman N Hello, did you join “e.MergeInstances” and before “e.Workbooks.Count”? I tried and still returned 0.